DAX Formula Help

[Anonymous]

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!

[kentyler]

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)