Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |