Need help with Dax formula please

Anonymous · ‎01-22-2021

Hello All,

I have been trying to create a Bleeders and Leaders (Delta) or Draggers and Drivers metric and it always gets blown up for the metric that has the lowest absolute value. For example, if Bleeders is -100 and Leaders is 50, I am able to get the desired result for Bleeders metrics but not Leaders. Below is a screen shot of the issue. I thought this was a LOD issue, and it still might be but i have rolled sales up into Department level from Item level and im still not able to get this solved. Any help would be much appreciated.

Notice the Total doesnt show up for Leaders because the absolute value is less than the bleeders, therefore having leaders values dividing into a total that is blank or zero and creating the result "infinity"

I can also post the dax formulas if needed.

Thank you for your help

Anonymous · ‎01-23-2021

Here are some of the Dax formulas used to create these metrics...

Sales = SUM(DailySalesTable[SalesAmt])

PY Sales = CALCULATE([Sales],DATEADD(Dates[Date],-1,YEAR))

YOY Sales = [Sales] - [PY Sales]

Sales Bleeders = IF([YOY Sales]<0,[YOY Sales])

Sales Bleeders % Mix = [Sales Bleeders]/CALCULATE([Sales Bleeders],ALLSELECTED(HierarchyTable))

Sales Leaders = IF([YOY Sales]>0, [YOY Sales])

Sales Leaders % Mix = [Sales Leaders]/CALCULATE([Sales Leaders],ALLSELECTED(HierarchyTable))

On the Sales Bleeders/Leaders % Mix, i have also tried ALLSELECTED(HierarchyTable[Department])) because the DailySalesTable contains Department level all the way down to Item level and i have a feeling this isnt working because of LOD and items having positive and negative sales.

Here is the schema... i also wonder if i create a table with the sales data rolled up so its not item levle if that will help. I would only want to see Bleeders and Leaders at Site, Dept or Cat level...