Totals not aligning - Aggregation issues?

[MasonGogoll]

Hello everyone,

I'm having some trouble here with how the totalling works within PowerBI.

The main point: When i export the dataset from the matrix widget and perform totals to the columns I get different and the right answers that I want. However, within PowerBI i get the wrong totals.

Essentially my aim is to measure client growth y-o-y but cutting out all the clients that were lost. To do this we are performing a calculation:

("Current period: retained client spend" - Demoninator) /

"Prior period: ONLY retained client spend that exist within "Current Period: retained client spend""

When we delineate out to client rows we are succesffully catching the Prior Year revenue in a LIKE4LIKE fashion. However, when it aggregates to the total at the bottom it is simply the same as the normal year on year total.

When I export the data under this widget into excel and perform sums on columns it is getting different totals to what is being expressed in the powerbi matrix widget. it is also expressing the correct answers I want!

PLEASE HELP

Picture of same dataset in excelPicture of matrix in PowerBI

[v-piga-msft]

Hi @MasonGogoll ,

If I understand your scenario correctly that the total is not correct when you create the matrix in excel with the dataset in power bi?

By my tests with the latest version of power bi, I cannot reproduce your issue.

If it is convenient, could you share a simple data sample which could reproduce your scenario so that we could have a test on it?

Best  Regards,

Cherry

[MasonGogoll]

Hi @v-piga-msft Cherry,

Thanks for getting back to me. I am trying to upload a file but it doesn't seem like I can. I am assuming there is something wrong with the dax I am using though. It appear that on a client level the dax works correctly. I have provided a screenshot below. Ou can see when know values exist in columns C + D (current period) it ignores the amounts in columns E + F (prior period) and returns a like for like value of 0 in columns G + H. However, on aggregate we can see that the totals are the same in the PowerBI matrix widget and different in the excel version???