Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help with Dax formula please

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"

 

Pic of B&L issue.jpg

 

I can also post the dax formulas if needed.

 

Thank you for your help

3 REPLIES 3
Anonymous
Not applicable

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...
 
WPCarey1985_0-1611452049500.png

 

 
StefanoGrimaldi
Resident Rockstar
Resident Rockstar

hey, 

yes paste a example dummy data and the current dax used that will help lots in having a good answer and asistence. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Anonymous
Not applicable

Ok let me build some dummy data and i will get back to you.  Thanks,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors