Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all!
I need to divide the difference between first and second column (Infeed-Discharge or Spoilage #) with the Sum of "Cupper" Infeed (highlighted in red). It needs to work both for top level and lower level of "Machine Type". It works now for top but not for bottom, as seen in "Spoilage %" column.
Hopefully I made myself clear. Would appreciate any help on this. Thanks much!
Solved! Go to Solution.
Hi @Anonymous ,
Modify the _total variable as below:
var _total=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Machine Type]="Cupper"))
For the whole dax expression is as below:
Measure =
var _discharge=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]="Discharge"))
var _infeed=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]="Infeed"))
var _total=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Machine Type]="Cupper"))
Return
IF(ISINSCOPE('Table'[Category]),SUM('Table'[Value]),FORMAT(DIVIDE(ABS(_discharge-_infeed),_total),"percent"))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
First transform table into below format:
Then create a measure as below:
Measure =
var _discharge=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]="Discharge"))
var _infeed=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]="Infeed"))
var _total=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Category]=MAX('Table'[Category])&&'Table'[Machine Type]=MAX('Table'[Machine Type])))
Return
IF(ISINSCOPE('Table'[Category]),SUM('Table'[Value]),FORMAT(DIVIDE(ABS(_discharge-_infeed),_total),"percent"))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi v-kelly-msft,
thanks for your feedback but the % calculation denominator should always be the "Cupper" Infeed - Sum of "sub set" where Machine Type = Cupper.
Hi @Anonymous ,
Modify the _total variable as below:
var _total=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Machine Type]="Cupper"))
For the whole dax expression is as below:
Measure =
var _discharge=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]="Discharge"))
var _infeed=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]="Infeed"))
var _total=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Machine Type]="Cupper"))
Return
IF(ISINSCOPE('Table'[Category]),SUM('Table'[Value]),FORMAT(DIVIDE(ABS(_discharge-_infeed),_total),"percent"))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi,
Does this measure work?
=divide([spoilage #],calculate([spoilage #],data[machine type]="Cupper"))
Hi Ashish_Mathur,
it does only for upper hierarchy level (bolded rows) - that's my Spoilage % column, but not for lower. Btw, the denominator should be the Sum of "Cupper" Infeed.
Hi,
Share the link from where i can download your PBI file.
@Anonymous
hope I understand your request clearly.
Diff = sum('Table (2)'[Infeed])-sum('Table (2)'[Dicharge])
Measure 3 = DIVIDE([Diff],CALCULATE(sum('Table (2)'[Infeed]),ALLEXCEPT('Table (2)','Table (2)'[Machine Type])))
Proud to be a Super User!
Hi ryan_mayu,
thanks for feedback!
You're close but the denominator for Measure 3 should always be the Sum of "Cupper" Infeed, when calculating any level or hierarchy.
User | Count |
---|---|
127 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |