Matrix Totals and subtotals not adding up

[RaelG]

Hi All,

I have been reading through a load of posts that evidently have similar sounding challenges but I just do not understand how to fix my specific issue.

My data is all about sales. I have historic data and based upon that I am trying to create an estimate for the remainder of the current year and also the year after. I am using a measure to calculate each quarter in isolation, partly because I am using slightly different logic for each quarter but also because I can't seem to make the data then split by quarter properly if I do it for a full year.

An example of the measure is as follows:

25E Rev Q1 =

CALCULATE(sum(Data[Rev USD]),Data[Year]="2024",Data[Quarter]="Q1",Data[SONOS]="Core")*(1+

(((CALCULATE(sum(Data[Rev USD]),Data[Year]="2023",Data[Quarter]="Q2")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2023",Data[Quarter]="Q3")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2023",Data[Quarter]="Q4")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2024",Data[Quarter]="Q1"))

-

(CALCULATE(sum(Data[Rev USD]),Data[Year]="2022",Data[Quarter]="Q2")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2022",Data[Quarter]="Q3")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2022",Data[Quarter]="Q4")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2023",Data[Quarter]="Q1")))

/

(CALCULATE(sum(Data[Rev USD]),Data[Year]="2023",Data[Quarter]="Q2")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2023",Data[Quarter]="Q3")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2023",Data[Quarter]="Q4")+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2024",Data[Quarter]="Q1"))))

+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2024",Data[Quarter]="Q1",Data[SONOS]="EOL")*0.5

+

CALCULATE(sum(Data[Rev USD]),Data[Year]="2024",Data[Quarter]="Q1",Data[SONOS]="NPI")*2

where you can see that 'Data' is my main table and Year, Quarter, SONOS and the value Rev USD are all contained with the same table. Is it obvious from this example what I am doing wrong or is there something else I can provide?

Thank you!

[amitchandak]

@RaelG , Based on the group vy field in your in you visual you have to create measure like

Sumx(Values(Table[Column]), [25E Rev Q1])

or

Sumx(Summarize(Table, Table[Col1], Dim[Col2]) , [25E Rev Q1] )

[RaelG]

Thanks @amitchandak  I used your first suggestion and I am much closer to my goal! When I use the field mentioned above (SONOS) the numbers work perfectly and all add up. However if I then add in a new field, to look at the data in a different way, I get mis-matches again. Is that because the measure was created using just the single field mentioned? If I want to see the numbers split by another field do I need to make a new list of measures like the one above for all the fields?

[RaelG]

Following up on this. My experiments have failed to get the different fields line up with the same totals which leads me to believe that it is not possible to have these SUMX's work the same but with different dimensions. So for example if I am projecting sales for next year based upon the growth from the last couple of years, and I group products into new, old and others, i can't see that data (let's say it totals 300) totalling the same if i want to see it, for example, by country.