Wrong Subtotals Matrix

[okusai3000]

Hello everyone,

First of all, I promise you that I've been using the searchbar but I couldn't find a proper answer.

I have the following graph, and I simply cannot get a proper subtotal. For instance, check out "AIROX". It says 82, but clearly is not the right sum.

The measure is the following one:

Cant Equipos Antiguos = CALCULATE([*Cant Equipos];filter(values('Dim Descripcion Equipos y Fungibles'[Marca]);[*Antiguedad]<-180))

And I tried things as *Fixed Measure= if(HASONEVALUE('Fact Equipos'[Article_ID]..., but it doesn't work.

Any idea?

thanks!!

[AllisonKennedy]

Can you please share your model view? Looks like you're using two tables in this matrix - Fact Equipos and Dim Descripcion Equipos y Fungibles. Which table is Cant Equipos doing a count from?

[okusai3000]

@AllisonKennedy There you go!

Thanks!

[okusai3000]

Anyone ? 😞

[AllisonKennedy]

@okusai3000 thanks for the data model - that's helpful.

Can you also please share the DAX for [cant equipos]? Since it is a measure, it can be doing anything - it doesn't actually matter which table it sits in within the data model, but does matter what the DAX looks like.

[okusai3000]

@AllisonKennedy , actually here's the PBIX https://we.tl/t-D9gxf5wV8a

thanks!

[okusai3000]

Here goes another up!

[AllisonKennedy]

@okusai3000 I have been working on this a bit, but you have a very complex web of measures referencing measures, which makes it tricky to troubleshoot. Can you try using words to explain the purpose of your key measures - Cant Equipos, Antiguedad, Cant Equipos Antiguedad.

Please explain with specific reference to context, so what should be happening at the Brand and Sap code levels. I think a simplification of some of your other measures that are 'working' at the moment might help make this problem easier.

[okusai3000]

Hi @AllisonKennedy ,

The only relevant measures are

*Cant Equipos; ---> Counts the last movement of each ID (esap_cod). 

*Antiguedad; --> Checks how much time has passed since the last movement of each ID

*Cant Equipos Antiguos -->Tries to check how many units are older than 180 days.

The issue is with *Cant Equipos Antiguos, where the subtotals doesn't seem to work properly....

Is that more clear?

thank you!

[AllisonKennedy]

Thanks @okusai3000
Not really much more clear though, as in your pbix the Cant Equipos refers to another date measure, so what do you mean by 'last movement'? You're doing some complex calculations based on date filters.

Also, the Cant Equipos is doing a Distinct count, which has a big impact on subtotals, so need to consider context and how to count a esap_cod if it falls into two Marcas, though that will primarily impact your grand totals I think in this case rather than subtotals.

[okusai3000]

Hi @AllisonKennedy ,

*Cant Equipos = calculate(distinctCOUNT('Fact Stock Equipos'[esap_cod]);

filter(all('Dim Date');

('Dim Date'[Date] <= max('Dim Date'[Date] ))))

Let me explain it. The DB shows the stock of some articles. We have a row per single article (esap_code) movement, and the "kind" of article is called "mmap_id". Everytime that one of them "moves", I mean, if is in stock, or is being used, it will be reflected on the DB, showing the date(fecha) and the new state (aprt_estado)

This is the table:

Fecha --  nmap_id -- esap_cod  -- aprt_estado

Thus, if I want to see the stock of the esap_cod category "Spoons", I need to COUNT all the LAST movement of each esap_code (every single Spoon )until a certain date.

Is maybe more clear?

Thanks!!

[AllisonKennedy]

That kind of makes sense, but then why are we counting esap_cod and not counting nmap_id?

[okusai3000]

@AllisonKennedy , Indeed, while I was writing you the post, that caught my attention. I have just tried with the nmap_id, and this makes problems with the subtotals of the kind of article (esap_cod). 

EDIT: @AllisonKennedy , So sorry, but the mmap_id, actually... is the kind of article!, and esap_Cod EACH article.... that's why the measure was done in that way....

[AllisonKennedy]

From my tests with your sample file, those are the correct subtotals for the Marca - you only have 3 distinct values for nmap within Airox: E18, E63, E65.

Maybe you need to combine esap and nmap to count distinct values for those? Something like using this: sumx(VALUES('Fact Stock Equipos'[esap_cod]), distinctCOUNT('Fact Stock Equipos'[mmap_id]))

for your cant equipos?

That doesn't fully solve your problem, but I think we're getting closer.

[okusai3000]

@AllisonKennedy , did you reach to read my last message? I was saying that at the end, the original formula was all right. I mean, the reason why I'm using DISTINCTCOUNT with esap_cod is because, esap_cod is the article itself (the unit), and nmap_id the category.

Any extra advice? 😕

thanks!

[okusai3000 · Accepted solution]

@AllisonKennedy ,

Finally with our consultant we arrived to the solution: We had to disable the relationship bewtween the calendar table (Dim Date) and the Fact Table (Fact Stock Equipos). This allowed us to have a proper subtotal, and in order to keep things ok with the original measures, I just add USERELATIONSHIP on them.

Anyway, thanks a lot for your kindness and help, 

[AllisonKennedy]

Yay! Glad it's working now and thanks for the update - sorry it has been a busy week. Please mark your own reply as a solution so people with a similar problem can benefit from it. 🙂

[Greg_Deckler]

@okusai3000 Have you read through this? This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

You would need to SUMMARIZE or GROUPBY on both of the columns in your rows hiearchy of your matrix.

[amitchandak]

@okusai3000 , Try like

CALCULATE(Sumx(filter(values('Dim Descripcion Equipos y Fungibles'[Marca]);[*Antiguedad]<-180);[*Cant Equipos]))

[okusai3000]

@amitchandak , Didn't work. I mean, is still showing the same.

@Ronald123 , I already followed her video and she talks about the use of HASONEVALUE. If I use that, it also doesn't work.

[amitchandak]

@okusai3000 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.