fix measure to work on all rows

[Anonymous]

hi guys,

i want to create a measure that works on all row fileds in the pivot table below. The measure has to be calculated on sum of values of "pz kit" considering only the rows that have min value of "tipo accessorio" (excluding 0) for each"item kit".

Below I've highlighted the rows that has to be sum up on the data set and on pivot table in green where the measure is working and in red when is not returning a correct value when you collapse the data on that level or calculate grand total.

i've tried to create a measure but i can't exclude 0s and make it works as i expect. Can you help me please?

here's the link of the excel file https://1drv.ms/u/s!AlibJbtIbDxpiN8WlkZNdQWH5Zi55g?e=mBQ8ct

[dax]

Hello @alledem ,

You could check my sample for more information

Saludos
Zoe Zhi

If this post helps, please consider accepting it as the solution to help other members find it more quickly.

[Anonymous]

can you please share the measure syntax? i can't open the report because i'm using power pivot on excel and i'm not allowed to install powerbi desktop

[dax]

Hi @alledem ,

Here is measure

Measure = sumx( SUMMARIZE('Table','Table'[famiglia],'Table'[tipo ordine],'Table'[modello],'Table'[item kit], "min", CALCULATE(MIN('Table'[pz kit]), FILTER('Table','Table'[pz kit]<>0))), [min])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

You can get rid of zeros by having a RETURN statement like the following:

RETURN

  IF(__SomeVariable = 0,BLANK(),__SomeVariable)

The other 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

[Anonymous]

When using SUMMARIZE, we need to use SUMX before it in the measure. I’m with Sam, don’t think your ProdPermTerms # is correct. Don’t quite know if I have your measure set up correctly, but know for sure that you need to implement SUMX into your Summarize variable measure. I believe you are very close to