cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Wrong Totals CALCULATE AND SUMMARIZE

Hi all,

I have tried to figure out why my totals are wrong and it seems to be some normal problem in PB.

Well, I didn't find a solution for two complex measures in my model that have both totals wrong.

First one:

Excesso PDD 2 =
CALCULATE(
SUMX(CAR,CAR[Montante]),
CAR[Aging PDD 2]>=120,
CAR[Cliente] in VALUES('Ordens Bloq'[CLIENTE])
)

Second one:

CAR Grouped MAX = var _tab= SUMMARIZE(FILTER(CAR,AND(RELATED(Clientes[Canal])="GT",[Status CAR Dia]="A vencer")),CAR[Data CAR],CAR[Venc Líquid],"_2",SUM(CAR[Montante]))
return
SUMX(SUMMARIZE(_tab,CAR[Venc Líquid], "_1", maxX(_tab,[_2])),[_1])

The right total is the one in the right side.

Does anyone know how to solve this issue?

Thank you.
1 ACCEPTED SOLUTION
Microsoft Employee

I believe your measures could be simplified but hard to tell without seeing your model/pbix.  In any case, you should be able to use your existing measures in a measure like this to get the correct totals.

NewMeasure = SUMX(VALUES(Table1[Meso de Ano]), [Excesso PDD])

This assumes Excesso PDD is an existing measure.  This is a common pattern and should give you the correct results in both the rows and the total.  You can do the same for your second one too (VALUES of your Venc Liquid column in first term, and existing measure in second term).

I recently made a video explaining this if you want to take a look - https://www.youtube.com/watch?v=UJPBeiGBEg4&t=11s

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

7 REPLIES 7
Microsoft Employee

I believe your measures could be simplified but hard to tell without seeing your model/pbix.  In any case, you should be able to use your existing measures in a measure like this to get the correct totals.

NewMeasure = SUMX(VALUES(Table1[Meso de Ano]), [Excesso PDD])

This assumes Excesso PDD is an existing measure.  This is a common pattern and should give you the correct results in both the rows and the total.  You can do the same for your second one too (VALUES of your Venc Liquid column in first term, and existing measure in second term).

I recently made a video explaining this if you want to take a look - https://www.youtube.com/watch?v=UJPBeiGBEg4&t=11s

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper II

Hi @mahoneypat

You solution worked just fine to my measure.

Thank you very much for help.

PS: I liked you very video and channel.

Super User

Have you applied any external filter in this Scenario. I think that is impacting your right hand side Table visual but not the one with Summarize function

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Helper II

You are right. The measure function works just fine, but when I add some external filter the Total from Table or other, like card, visuals doesn't work.
Do you know how am I able to fix it?

Thank you.

Super User

You need to have binding factor between those Two Tables. and relate them , I mean the Summarized Table and the Fact Table. Create a relationship and it should work! let me know share your Kudoes

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Helper II

@VijayP

I have them (Client and Calendar).
But those measures use a pretty complex calculation as you may have seem.

I am trying to solve this without creating more tables to my model, but it seems I won't have another way. 😞

Super User

Just connect the Summarised table with the Dim Table as you connected the Fact Table

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!