PowerBI - Column output based on another column value and time function
I have a table like that:
I'm comparing values from CurrentMonth vs PreviousMonth (based on the month selected in the slicer) for all of the KPIs, and based on that, I assign a specific result.
However, for each KPI, there is a logic behind when comparing. The logic is:
So, for KPI A, the CurrentMonth value must be 10% higher then PreviousMonth. When this is true, then assign 1. If not true, then 0. For KPI B, it has to be 3% higher. And for KPI C, it just needs to be higher.
To pull the CurrentMonth and PreviousMonth values, I created two measures, one of them with a time function:
CurrentMonth = SUM( [KPIValue] )
and
PreviousMonth = CALCULATE (
SUM( [KPIValue] );
PREVIOUSMONTH( [Date] )
)
I tried to create a Column in my table to assign the output (Result), and used a Switch function like this:
Result = SWITCH (
[KPIName];
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
My issue is that when I try to use this Switch, it does not work since the PreviousMonth comes as blank (it can not calculate a time function for each row of the table - only works as a measure).
I'm now lost how should I proceed from here. I tried to create a Measure instead of a Column, but then the Switch function does not accept the KPIName as a parameter.
Any ideas? Hope I've been clear here, if not, let me know what further information you need.
switch-statement powerbi calculated-columns powerbi-desktop
edited Nov 27 '18 at 9:21
Ram Koti
1,95842029
asked Nov 27 '18 at 9:03
KreivenKreiven
186
add a comment |
I have a table like that:
I'm comparing values from CurrentMonth vs PreviousMonth (based on the month selected in the slicer) for all of the KPIs, and based on that, I assign a specific result.
However, for each KPI, there is a logic behind when comparing. The logic is:
So, for KPI A, the CurrentMonth value must be 10% higher then PreviousMonth. When this is true, then assign 1. If not true, then 0. For KPI B, it has to be 3% higher. And for KPI C, it just needs to be higher.
To pull the CurrentMonth and PreviousMonth values, I created two measures, one of them with a time function:
CurrentMonth = SUM( [KPIValue] )
and
PreviousMonth = CALCULATE (
SUM( [KPIValue] );
PREVIOUSMONTH( [Date] )
)
I tried to create a Column in my table to assign the output (Result), and used a Switch function like this:
Result = SWITCH (
[KPIName];
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
My issue is that when I try to use this Switch, it does not work since the PreviousMonth comes as blank (it can not calculate a time function for each row of the table - only works as a measure).
I'm now lost how should I proceed from here. I tried to create a Measure instead of a Column, but then the Switch function does not accept the KPIName as a parameter.
Any ideas? Hope I've been clear here, if not, let me know what further information you need.
switch-statement powerbi calculated-columns powerbi-desktop
edited Nov 27 '18 at 9:21
Ram Koti
1,95842029
asked Nov 27 '18 at 9:03
KreivenKreiven
186
add a comment |
I have a table like that:
I'm comparing values from CurrentMonth vs PreviousMonth (based on the month selected in the slicer) for all of the KPIs, and based on that, I assign a specific result.
However, for each KPI, there is a logic behind when comparing. The logic is:
So, for KPI A, the CurrentMonth value must be 10% higher then PreviousMonth. When this is true, then assign 1. If not true, then 0. For KPI B, it has to be 3% higher. And for KPI C, it just needs to be higher.
To pull the CurrentMonth and PreviousMonth values, I created two measures, one of them with a time function:
CurrentMonth = SUM( [KPIValue] )
and
PreviousMonth = CALCULATE (
SUM( [KPIValue] );
PREVIOUSMONTH( [Date] )
)
I tried to create a Column in my table to assign the output (Result), and used a Switch function like this:
Result = SWITCH (
[KPIName];
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
My issue is that when I try to use this Switch, it does not work since the PreviousMonth comes as blank (it can not calculate a time function for each row of the table - only works as a measure).
I'm now lost how should I proceed from here. I tried to create a Measure instead of a Column, but then the Switch function does not accept the KPIName as a parameter.
Any ideas? Hope I've been clear here, if not, let me know what further information you need.
switch-statement powerbi calculated-columns powerbi-desktop
edited Nov 27 '18 at 9:21
Ram Koti
1,95842029
asked Nov 27 '18 at 9:03
KreivenKreiven
186
I have a table like that:
I'm comparing values from CurrentMonth vs PreviousMonth (based on the month selected in the slicer) for all of the KPIs, and based on that, I assign a specific result.
However, for each KPI, there is a logic behind when comparing. The logic is:
So, for KPI A, the CurrentMonth value must be 10% higher then PreviousMonth. When this is true, then assign 1. If not true, then 0. For KPI B, it has to be 3% higher. And for KPI C, it just needs to be higher.
To pull the CurrentMonth and PreviousMonth values, I created two measures, one of them with a time function:
CurrentMonth = SUM( [KPIValue] )
and
PreviousMonth = CALCULATE (
SUM( [KPIValue] );
PREVIOUSMONTH( [Date] )
)
I tried to create a Column in my table to assign the output (Result), and used a Switch function like this:
Result = SWITCH (
[KPIName];
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
My issue is that when I try to use this Switch, it does not work since the PreviousMonth comes as blank (it can not calculate a time function for each row of the table - only works as a measure).
I'm now lost how should I proceed from here. I tried to create a Measure instead of a Column, but then the Switch function does not accept the KPIName as a parameter.
Any ideas? Hope I've been clear here, if not, let me know what further information you need.
switch-statement powerbi calculated-columns powerbi-desktop
switch-statement powerbi calculated-columns powerbi-desktop
edited Nov 27 '18 at 9:21
Ram Koti
1,95842029
asked Nov 27 '18 at 9:03
KreivenKreiven
186
edited Nov 27 '18 at 9:21
Ram Koti
1,95842029
asked Nov 27 '18 at 9:03
KreivenKreiven
186
edited Nov 27 '18 at 9:21
Ram Koti
1,95842029
edited Nov 27 '18 at 9:21
Ram Koti
1,95842029
edited Nov 27 '18 at 9:21
Ram Koti
1,95842029
1,95842029
asked Nov 27 '18 at 9:03
KreivenKreiven
186
asked Nov 27 '18 at 9:03
KreivenKreiven
186
asked Nov 27 '18 at 9:03
KreivenKreiven
186
186
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Found the solution. As a Measure (not column), include SELECTEDVALUE to be able to retrieve KPIName.
Result = SWITCH (
SELECTEDVALUE([KPIName]);
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
answered Nov 27 '18 at 10:40
KreivenKreiven
186
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53496036%2fpowerbi-column-output-based-on-another-column-value-and-time-function%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Found the solution. As a Measure (not column), include SELECTEDVALUE to be able to retrieve KPIName.
Result = SWITCH (
SELECTEDVALUE([KPIName]);
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
answered Nov 27 '18 at 10:40
KreivenKreiven
186
add a comment |
Found the solution. As a Measure (not column), include SELECTEDVALUE to be able to retrieve KPIName.
Result = SWITCH (
SELECTEDVALUE([KPIName]);
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
answered Nov 27 '18 at 10:40
KreivenKreiven
186
add a comment |
Found the solution. As a Measure (not column), include SELECTEDVALUE to be able to retrieve KPIName.
Result = SWITCH (
SELECTEDVALUE([KPIName]);
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
answered Nov 27 '18 at 10:40
KreivenKreiven
186
Found the solution. As a Measure (not column), include SELECTEDVALUE to be able to retrieve KPIName.
Result = SWITCH (
SELECTEDVALUE([KPIName]);
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)
answered Nov 27 '18 at 10:40
KreivenKreiven
186
answered Nov 27 '18 at 10:40
KreivenKreiven
186
answered Nov 27 '18 at 10:40
KreivenKreiven
186
answered Nov 27 '18 at 10:40
KreivenKreiven
186
186
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53496036%2fpowerbi-column-output-based-on-another-column-value-and-time-function%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
