Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX Formula Help

Scenario: I work for a CPG company that is building out our BI dashboards. One thing we like to look at is our performance in a given retailer compared to that retailer's rest of market (ROM). I'm trying to write a formula that does this, but am coming up short.

 

The user picks the retailer via a slicer at the top of the page.

 

Here is what I have so far: 

 

I created a calculated column in Table1 that pulls in the ROM value for each given retailer and named it [ROM]

 

Working formula:

CALCULATE(SUM('Table1'[Dollars])/SUM('Table1'[LY Dollars])-1,FILTER(ALL('Table1'),'Table1'[Geography]=[ROM]))
 
Table1
[Geography]               [Dollars]      [LY Dollars]
Retailer 1                    xxx.xx           xxxx.xx
Retailer 2
Retailer 1 ROM
Retailer 2 ROM
etc.
 
Table2
[Geography]             [ROM]
Retailer 1                  Retailer 1 ROM
 
Thanks!

1 Reply

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    It's not clear (at least to me) from your example what data you are starting with.

    Normally you would calculate the 2 values you want to compare and store them in VARs

    Then do the division using the 2 VARs

    That also allows you to return each var independantly in order to debug your progress

     

    Performance Against ROM =

    VAR ourperformance = ..some calculation

    VAR ROM = ...som calculation

    RETURN DIVIDE(ourperformance,ROM)