topic Re: Running total of % of Total in DAX Commands and Tips

Running total of % of Total

Susan13 — Tue, 10 Mar 2020 03:19:23 GMT

Hi,

I am trying to show the running total for % of total. In the image below the red line is the actual % of total. But I want to sum these together, like the yellow line but the values are incorrect.

This is my current measure however I am not sure where it is wrong. I have tried other solutions that have been provided but was unable to get them to work.

PL RunningTotal = MAXX ( vwPBIPourLineData, SUMX ( FILTER ( SUMMARIZE ( CALCULATETABLE ( vwPBIPourLineData, ALLEXCEPT ( vwPBIPourLineData, vwPBIPourLineData[WeekNumber] ), ALLSELECTED ( vwPBIPourLineData[WeekNumber] ) ), vwPBIPourLineData[PartNumber], "AbsDiff", SUM ( vwPBIPourLineData[AbsoluteDiff] ) ), [AbsDiff] >= SUMX ( FILTER ( CALCULATETABLE ( vwPBIPourLineData, ALLEXCEPT ( vwPBIPourLineData, vwPBIPourLineData[WeekNumber] ), ALLSELECTED ( vwPBIPourLineData[WeekNumber] ) ), vwPBIPourLineData[PartNumber] = EARLIER ( vwPBIPourLineData[PartNumber], 2 ) ), vwPBIPourLineData[AbsoluteDiff] ) ), [AbsDiff] ) )

Re: Running total of % of Total

Anonymous — Tue, 10 Mar 2020 10:13:14 GMT

Technically, you're not doing a running total because a RT is performed against TIME. You are trying to aggregate across a different dimension. For this, you need to be able to sort the members of the dimension exactly as the visual sorts it and then aggregate accordingly.

Best
D

Re: Running total of % of Total

Anonymous — Tue, 10 Mar 2020 10:34:27 GMT

// Say that you want to have a "RT" // across products and you want to // sort them by a measure. Call the // measure [Base Measure]. // Instead of products, you could // write a very similar measure // that would give you a RT across // product categories, for instance. [Base Measure - Product RT] = // Products should be on your x-axis // and sorted according to [Base Measure] // descending. var __totalVisible = CALCULATE( [Base Measure], ALLSELECTED( Products ) ) var __singleProductVisible = HASONEVALUE( Products[ProductId] ) var __currentProductValue = [Base Measure] // Get the value of the base measure // for all products before and including // this one sorted by base measure // descending. var __totalForProductsUpToThisOne = SUMX( ALLSELECTED( Products ), var __iteratedProdValue = [Base Measure] var __shouldAdd = __iteratedProdValue >= __currentProductValue return __iteratedProdValue * __shouldAdd ) var __runningTotalPercentage = DIVIDE( __totalForProductsUptoThisOne, __totalVisible ) return if( __singleProductVisible, __runningTotalPercentage )

You have to think about how to deal with ties, that is, when 2 different products have the same [Base Measure].

Best

Re: Running total of % of Total

Susan13 — Thu, 19 Mar 2020 17:19:28 GMT

Thanks, I tried this method but still no luck. Any other recommendations are welcome.

Re: Running total of % of Total

Anonymous — Fri, 20 Mar 2020 18:15:18 GMT

He there. It works for me:

https://1drv.ms/u/s!ApyQEauTSLtOgYMzWBdXOT0iIMwh6Q?e=MtblEU

Best