Forum Discussion
Divide Measures
Hello,
I have a somewhat similar issue:
I’m required to divide the aggregated sum of two values in two different Periods (P6/ P12) and Scenarios (Actual/Budget). The issue is how to store the values and then utilise them in the final measure - Actual#MAP131_ARO while using only P6 in the Period Slicer or preferrably P6 and P12 in the Visual Filter
- A#MAP131_P6 =
CALCULATE(
SUM(GRA_extract[Value]),
FILTER(GRA_extract, GRA_extract[Account]="MAP131"),
FILTER (GRA_extract, GRA_extract[Currency]="GBP"),
NOT(GRA_extract[ServiceLine]) IN {"AllCustom2","TotalUnit","AboveUnit", "TotalAbove", "TotalServices"},
NOT(GRA_extract[Sector]) IN {"AllCustom1","TotalUnit","TotalSectors"},
FILTER ( GRA_extract, GRA_extract[Custom3] = "IFRS100PC" ),
FILTER ( GRA_extract, GRA_Extract[Period] = "P6" )
)
Result = 12623440.522209276
- A#MAP131_P12 =
CALCULATE(
SUM(GRA_extract[Value]),
FILTER(GRA_extract, GRA_extract[Account]="MAP131"),
FILTER (GRA_extract, GRA_Extract[Scenario]="Budget"),
NOT(GRA_extract[ServiceLine]) IN {"AllCustom2","TotalUnit","AboveUnit", "TotalAbove", "TotalServices"},
NOT(GRA_extract[Sector]) IN {"AllCustom1","TotalUnit","TotalSectors"},
FILTER ( GRA_extract, GRA_extract[Custom3] = "IFRS100PC" ),
FILTER ( GRA_extract, GRA_Extract[Period] = "P12" )
)
Result = 36209485.11878476
Actual#MAP131_ARO = ([A#MAP131_P6]/[A#MAP131_P12])
The final result is Infinity
I'd appreciate your kind advice.