Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi @chotu27
I'm doing it as a calculated measure this time.
Measure = VAR LastVisit = CALCULATE( MAX('Table2'[Visit Name]), FILTER( ALL('Table2'), 'Table2'[Subject Number] = MAX('Table2'[Subject Number]) && 'Table2'[Visit Name] < MAX('Table2'[Visit Name]) )) VAR Visit1 = CALCULATE( SUM('Table2'[Score]), FILTER( ALL('Table2'), 'Table2'[Subject Number] = MAX('Table2'[Subject Number]) && 'Table2'[Visit Name] = LastVisit ) ) VAR Visit2 = SUM('Table2'[Score]) RETURN DIVIDE( Visit2 - Visit1 , Visit1 )
Hi @chotu27
This calculated column seems to do the trick with your sample data. I have attached a PBIX model.
% Change = VAR ThisValue = 'Table1'[Sales] VAR LastValue = MINX(FILTER('Table1','Table1'[Sales] > ThisValue),'Table1'[Sales]) RETURN IF(ISBLANK(LastValue),1, - DIVIDE( (LastValue - ThisValue ) , LastValue) )
@Phil_Seamark Good One ! But Some how it is not working for my dataset
Please check below data.
% change cal = current visit -previous visit/ previous visit
Suppose for S1051 calculation i s= Visit 2 - Visit 1/Visit1 which is 80-100/100 = - 0.20
I need a calculation like above
Hi @chotu27
I'm doing it as a calculated measure this time.
Measure = VAR LastVisit = CALCULATE( MAX('Table2'[Visit Name]), FILTER( ALL('Table2'), 'Table2'[Subject Number] = MAX('Table2'[Subject Number]) && 'Table2'[Visit Name] < MAX('Table2'[Visit Name]) )) VAR Visit1 = CALCULATE( SUM('Table2'[Score]), FILTER( ALL('Table2'), 'Table2'[Subject Number] = MAX('Table2'[Subject Number]) && 'Table2'[Visit Name] = LastVisit ) ) VAR Visit2 = SUM('Table2'[Score]) RETURN DIVIDE( Visit2 - Visit1 , Visit1 )
@Phil_Seamark Excellent It got Worked but some how i think my table in real contains so many columns so that is why it is not working for me.
Is your calculation works for only 3 columns or more than that in table?
HI @chotu27
When you say it isn't working, do you mean you are expecting a lot more in the final column in your screenshot?
@Phil_Seamark Thank you very much i got it worked finally . Your solution is smart
There is one more similar calculation i need
I wanted to compare values with previous row and flag it such as if current value = previous value then put 1 otherwise put 0
look at screen shot below
if visit 2 score is same as visit 1 score then 1 otherwise 0
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |