How to calculate columns based on slicer selection

[Anonymous]

Hi,

there is a slice its called target. user can select target between 1% and 99% .

i need to re-calculate dimention every time user select new value. this deminsion is used as legend in Stacked area chart.

formula looks like:

Payload Target Group = SWITCH(TRUE(),
'Table1'[Payload Target] =0 , "No Target"
,'Table1'[Payload] =0 , "NO Payload"
,DIVIDE('Table1'[Payload] , 'Table1'[Payload Target]) < (1- DIVIDE(Ds_target[Ds_TargetSelectedValuet],1)) , "Underload"
,DIVIDE('Table1'[Payload], 'Table1'[Payload Target]) > (1+ DIVIDE(Ds_target[Ds_TargetSelectedValuet],1) ), "Overload"
,"Within Limit"
)

Report:

Any idea how can i do it?

[amitchandak]

@Anonymous , You can not create a column, based on slicer selection.

You need to create a measure or do segmentation with measure

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
https://youtu.be/CuczXPj0N-k

[Anonymous]

these are dimensions 'Table1'[Payload] , 'Table1'[Payload Target]

i need to calculate it row by row

[v-kelly-msft]

Hi @Anonymous ，

Could you pls provide some sample data with expected output for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Please download file from below linke

https://drive.google.com/drive/folders/1_MvzUYXC9c6IMohfXrGAhyEjF1pmDjyt?usp=sharing

i made it more convenient, look at rate column

[aj1973]

@Anonymous 

what do you want to see as an Output?

Your measure and the tables don't existe in your Pbix file!

[Anonymous]

i made it simple. i need to calculate rate base on drop down value .

if grade = 100 rate is "Top"

grade + value >100 then "good"
grade + value >90 then "not bad"

[v-kelly-msft]

Hi  @Anonymous ,

You'd better create a measure instead of a calculated column,see below:

_rate = 
SWITCH(TRUE(),
MAX('Student'[grade])=100,"Top",
MAX('Student'[grade])+'TargetTable'[TargetSelectedValue]>100,"good",
MAX('Student'[grade])+'TargetTable'[TargetSelectedValue]>90,"not bad","try more")

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thank you, now its pretty

actually my formula is a bit complex, i added new column to source and its called Sclae , formula changed to 

_rate =
SWITCH(TRUE(),
MAX(Student[grade]) =100 , "Top",
DIVIDE( MAX(Student[grade]) ,MAX(Student[Scale])) < 1 - [TargetSelectedValue] , "Good",
DIVIDE( MAX(Student[grade]) ,MAX(Student[Scale])) > 1 + [TargetSelectedValue] , "Not Bad",
"try more"
)

result is not as my expectation

here is file

https://drive.google.com/drive/u/0/folders/1_MvzUYXC9c6IMohfXrGAhyEjF1pmDjyt

[aj1973]

@Anonymous 

What are your expectations? if this is the picture

Where 

X = DIVIDE( MAX(Student[grade]) ,MAX(Student[Scale]))

Y = 1 - [TargetSelectedValue]

Z = 1 + [TargetSelectedValue]

Thats what you have in your Formula __rate

_rate =
SWITCH(TRUE(),
MAX(Student[grade]) =100 , "Top",
X < Y , "Good",
X > Z , "Not Bad",
"try more"
)

[Anonymous]

i want to use new calculation as legend in stacked area chart. but cant add measure as legend

[aj1973]

Measures are not columns type of thing therefore they can't be used as legend 

[Anonymous]

so do you mean i can not create this report in powerbi ?
to summarize my issue:

1- there is drop down for target

2- Want to calculate target-value, measure should be created !

3- measure cant show info in legend !

is there any solution ?

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Create a dimension table as below:

If you wanna get an average grade in the related legend,create a measure as below:

_rate1 = 
var _Top=CALCULATETABLE(VALUES('Student'[grade]),FILTER(ALL(Student),'Student'[_rate]="Top"))
var _Good=CALCULATETABLE(VALUES('Student'[grade]),FILTER(ALL(Student),'Student'[_rate]="Good"))
var _NotBad=CALCULATETABLE(VALUES('Student'[grade]),FILTER(ALL(Student),'Student'[_rate]="Not Bad"))
var _Tryagain=CALCULATETABLE(VALUES('Student'[grade]),FILTER(ALL(Student),'Student'[_rate]="Try again"))
Return
 SWITCH(
    SELECTEDVALUE('dim table'[Category]),
    "Top",AVERAGEX(_Top,[grade]),
    "Good",AVERAGEX(_Good,[grade]),
    "Not Bad",AVERAGEX(_NotBad,[grade]),
    "Try again",AVERAGEX(_Tryagain,[grade]))

Put the dimension field as legend,and you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi @Anonymous ,

Yes,measure cant be used as  legend,but calculated column can,the link of your .pbix file is not available,could you pls reshare it?If possible,could you pls also attach your expected output?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Link : https://drive.google.com/drive/folders/1_MvzUYXC9c6IMohfXrGAhyEjF1pmDjyt?usp=sharing

1- so there is not any way to create calculated column based on target (drop down list) ?

2- need to upgrade rate formula to

_rate =
SWITCH(TRUE(),
MAX(Student[grade]) =100 , "Top",
DIVIDE( MAX(Student[grade]) ,MAX(Student[Scale])) < 1 - [TargetSelectedValue] , "Good",
DIVIDE( MAX(Student[grade]) ,MAX(Student[Scale])) > 1 + [TargetSelectedValue] , "Not Bad",
"try more"
)

[aj1973]

"You can not create this report in Power BI?" With Power Bi you can create inimaginable kinds of report. 

Your Summary of the issues are not very clear eventhough I think we helped you with it.

Maybe there is some limitation for now as Microsoft is updating Power Bi every month so you might as well through a ticket to Microsoft for a better solution.