Total Row of Measure not summing as expected

[MKMayers]

Hi,

We have to pay the council PRN costs for the materials we use in our packaging. We pay this price per case sold, so Cases * PRN Price per case  = PRN Costs to pay.

These costs changed in April 2023, so what I want to do is for anything before April 2023 I want to do “Volume (cases)” x “PRN Cost per case before” and anything after April 2023 I want to do “Volume (cases)” x “PRN Cost per case after”.

For every row I have created a column that calculated the cost using the price before April 2023 (PRN Cost v4 before) and another column looking at the cost using the price after April 2023 (PRN Cost v4 after).

I have then created the measure PRNv5. This measure is a switch statement that says if the date is before April 2023 then use the value from “PRN Cost v4 before” and if the date is after April 2023 then use the value from “PRN Cost v4 after”

This measure appears to work at the row level however tht Total Row is not equal to the sum of the PRNv5 Column. £249.50 + £167.64 is £417.14, not £426.50 as the Total row suggests. I can't use the sumx() function as it is a measure. However I can't seem to create PRNv5 as a column at all. 

Any help would be much appreciated!

Thanks 

Michael 

[MKMayers]

Thanks for the suggestion @amalsperera, but I have tried that and I get the "cannot be pushed to the remote data and cannot be used in this scencrio". I think becuase the tables are in different source groups. I don't know if there is a workaround for that?

Thanks

Michael 

[amalsperera]

Hi @MKMayers ,

In your measure check if the date is before or after april and use two calculation formulas for the total.

Total = if(date<4/1/2023 , sum(formula 1) , sum(formaula 2))

Regards

Amal