Bad Total for Measure

[John Jairo V]

Greetings to all!

I have several Measures in PowerBI. All the calculation seems good... except for Total of measure: "MOD Pond KG Año Actual".

I Have three tables into MIX PROD Canvas:
1. PlantaCeco - Total KG Año Actual
2. Linea - Total KG Año Actual
3. Descripción - Total KG Año Actual - % Part Lineas Año Actual - MOD Pond KG Año Actual

In this example:
I filter by PlantaCeco "Chicles" in First Table, and Linea "BOLAS" in Second Table

The problem is in third table, in MOD Pond KG Año Actual Measure.
I filter by Linea "BOLAS", and I need real SUM in this measure in third table:
Now, the Total give me 5204.09. I need the Total give me the real SUM: 1917.58 + 502.68 + 35.15 = 2455.410

I need really help on this. Thx for your answers.

 

[GraH - Guido]

Hi John,
The formula used is
MOD Pond KG Año Actual = [Total KG Año Actual] * [% Part KG Lineas Año Actual]
So 5204.09 * 100% = 5204.09
I guess in Excel you would use sumproduct to have the correct pondaration.

Could you use SUMX construction where you apply the % on each total total row? It has been an while since I wrote some DAX, so I just failed , but I reckon you can pull it off.

 

[John Jairo V]

Hi Guido!

Can You help me with the DAX fórmula? I need some help on this. Blessings!

 

[Chihiro]

Try...

MOD Pond KG Año Actual = SUMX(fMix,[Total KG Año Actual]*[% Part KG Lineas Año Actual])

Note: You may want to test few other alternatives and check for performance. SUMX is iterator function and could be expensive. Test calculated columns, changing model structure etc. It may also be beneficial to change your data model to star schema model (i.e. 1 Fact table and multiple dimension tables).

Edit: One other method is to use =IF(HASONEVALUE(RowLabelColumn),Standard Calc, Special Calc for Total). But I haven't had the time to step through all your measure to follow calculation dependencies etc. You can test how it works by adding measure:
Check=IF(HASONEVALUE(fMix[Descripción]),1,0)

See link for more detail on this function.
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

 

[John Jairo V]

Great @Chihiro . Really appreciate the help. Works really fine, and thx for your suggestion. Thx Guido for your answer too... Blessings!

 

[GraH - Guido]

Try...

MOD Pond KG Año Actual = SUMX(fMix,[Total KG Año Actual]*[% Part KG Lineas Año Actual])

Oh my.... Spotting my error, I completely forgot the table reference in the formula.

Hi Guido!

Can You help me with the DAX fórmula? I need some help on this. Blessings!

Got beaten by Chihiro, but I was kind of hoping he would suggest a solution. Where I'm the amateur, he is the pro ;-)