Calculate % of Difference from higher hierarchy level (Sum)

[Anonymous]

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!

[v-kelly-msft]

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!

[Anonymous]

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.

[v-kelly-msft · Accepted 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!

[Ashish_Mathur]

Hi,

Does this measure work?

=divide([spoilage #],calculate([spoilage #],data[machine type]="Cupper"))

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[ryan_mayu]

@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])))

[Anonymous]

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.