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
I have the following data
Type | PoR | Amount | Month |
A | Real | 5 | 1 |
A | Real | 4 | 2 |
A | Real | 5 | 3 |
A | Real | 8 | 4 |
A | Real | 9 | 5 |
A | Plan | 2 | 1 |
A | Plan | 3 | 2 |
A | Plan | 6 | 3 |
A | Plan | 3 | 4 |
A | Plan | 7 | 5 |
B | Real | 6 | 1 |
B | Real | 7 | 2 |
B | Real | 7 | 3 |
B | Real | 7 | 4 |
B | Real | 10 | 5 |
B | Plan | 5 | 1 |
B | Plan | 5 | 2 |
B | Plan | 7 | 3 |
B | Plan | 8 | 4 |
B | Plan | 8 | 5 |
And with a measure i need to create the following absolute cumulative graph
the graph show the cumulative column by type and month. The cumulative column is the absolute value of Real - Plan for each type
Im Using this measure to create the Absolute column of Real - Plan
ABS Real-Plan = abs(CALCULATE ( SUM ( Table[Amount] ); Table[PoR] = "Real" ) - CALCULATE ( SUM ( Table[Amount] ); Table[PoR] = "Plan" ))
and im using this measure for the cumulative column that i'm graphing
Cumulative = CALCULATE ( SUMX (Table; [ABS Real-Plan] ); FILTER ( ALLSELECTED ( Table[Month] ); Table[Month] <= MAX ( Table[Month] ) )
but i'm getting a strange results in the grad total on the table and in the graphs numbers. EXAMPLE for type A
Thanks
Solved! Go to Solution.
Create these measures instead
ABS Real-Plan = ABS ( CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Month] ), 'Table'[PoR] = "Real" ) ) - CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Month] ), 'Table'[PoR] = "Plan" ) ) ) Cumulative = SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type] ) && 'Table'[PoR] = MAX ( 'Table'[PoR] ) && [Month] <= MAX ( [Month] ) ), [ABS Real-Plan] )
Best Regards
Maggie
Create these measures instead
ABS Real-Plan = ABS ( CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Month] ), 'Table'[PoR] = "Real" ) ) - CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Month] ), 'Table'[PoR] = "Plan" ) ) ) Cumulative = SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type] ) && 'Table'[PoR] = MAX ( 'Table'[PoR] ) && [Month] <= MAX ( [Month] ) ), [ABS Real-Plan] )
Best Regards
Maggie
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |