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 there,
I have a table that contains 3 rows.
Is there a way I can show rows 2 & 3 as percentage differences from row 1?
Thanks,
JAson
Solved! Go to Solution.
Hi @Anonymous,
Create the measures as below.
Measure = CALCULATE(SUM(Table1[t1]))
rank = RANKX(ALL(Table1),[Measure])
perc = var onerow = CALCULATE(SUM(Table1[t1]),FILTER(Table1,[rank]=3)) var restrow = CALCULATE(SUM(Table1[t1]),FILTER(Table1,[rank]<>3)) return restrow/onerow
For more details, please check the pbix as attached.
Regards,
Frank
Hi there,
I want to show % differences between my control and tests.
Imagine a table like this:
Control 25
Test A 30
Test B 18
I want to know as another column what Test A and then Test B are as a percentage of Control. How can I do this?
Thanks
Hi @Anonymous,
Create the measures as below.
Measure = CALCULATE(SUM(Table1[t1]))
rank = RANKX(ALL(Table1),[Measure])
perc = var onerow = CALCULATE(SUM(Table1[t1]),FILTER(Table1,[rank]=3)) var restrow = CALCULATE(SUM(Table1[t1]),FILTER(Table1,[rank]<>3)) return restrow/onerow
For more details, please check the pbix as attached.
Regards,
Frank
Hi @Anonymous,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
Hi @Anonymous,
I made one sample for your reference.
1. Insert an index column in Power query.
2, Create a measure as below.
perc = VAR row1 = CALCULATE ( SUM ( Table1[t1] ), FILTER ( Table1, Table1[Index] = 1 ) ) + CALCULATE ( SUM ( Table1[t2] ), FILTER ( Table1, Table1[Index] = 1 ) ) + CALCULATE ( SUM ( Table1[t3] ), FILTER ( Table1, Table1[Index] = 1 ) ) VAR restrow = CALCULATE ( SUM ( Table1[t1] ), FILTER ( Table1, Table1[Index] <> 1 ) ) + CALCULATE ( SUM ( Table1[t2] ), FILTER ( Table1, Table1[Index] <> 1 ) ) + CALCULATE ( SUM ( Table1[t3] ), FILTER ( Table1, Table1[Index] <> 1 ) ) RETURN row1 / restrow
For more details, please check the pbix as attached.
Regards,
Frank
This is really useful and thanks so much.
The only issue I have here is that the values I'm trying to calculate differences from are already measures.
So as an example (Table1[t1]) would be a calculated measure already.
Thanks,
JAson
CALCULATE ( SUM ( Table1[t1] )
@Anonymous Sample data and expected output will be really helpful to understand it better and also to solve the issue quicker.
Proud to be a PBI Community Champion
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |