Incorrect Measure Total on Matrix

JMuirhead28 — Thu, 31 Aug 2023 12:43:50 GMT

Hello,

I have a matrix in a report that contains a number of measures, unfortunately the measure total is incorrect for each one. I have been trying to find a solution but have so far had no luck, hence I am turning to the community to help.

I unfortunately cant post the file as it contains business sensitive information, but will do my best to explain without it.

Below is my final matrix, which has the correct monthly values but incorrect total:

This is the formula used for each measure:

Reforecast_Value_Ardley =

CALCULATE(SUM(Anaplan_Electricity_Revenue[Value]), Anaplan_Electricity_Revenue[PPM_Name]="Ardley ERF", Anaplan_Electricity_Revenue[Version]="Budget FY24", Anaplan_Electricity_Revenue[Value_Type]="Electricity Revenue - Grid")+

((CALCULATE(SUM(Volume_Hierachy_Final[Total_Volume]), Volume_Hierachy_Final[PPM_Name]="Ardley ERF") - CALCULATE(SUM(Anaplan_Volume_Daily[Value]), Anaplan_Volume_Daily[PPM_Name]="Ardley ERF", Anaplan_Volume_Daily[Version]="Budget FY24"))*

AVERAGE(Reforecast_Power_Price[Reforecast_Price]))

The formula filters tables and sums the data, then multiplies the data by a monthly price.

I have been scouring the forums and it seems like the HASONEFILTER could be used to help with this issue, but I cant seem to get it to work.

Let me know if any more information is required.

Any help would be greatly appreciated.

Thanks in advance,
Josh

Re: Incorrect Measure Total on Matrix

JMuirhead28 — Thu, 31 Aug 2023 15:41:04 GMT

All,

I have managed to resolve the issue with the total. Rather than using a simple multiplication, I created a measure for the middle subtraction and then used SUMX to multiply this value by the price and it has worked.

Cheers,
Josh

topic Re: Incorrect Measure Total on Matrix in Report Server

Incorrect Measure Total on Matrix

Re: Incorrect Measure Total on Matrix