DAX Formula Help

Anonymous — Wed, 22 Jan 2020 18:27:40 GMT

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!

Re: DAX Formula Help

kentyler — Thu, 23 Jan 2020 03:06:38 GMT

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)

topic DAX Formula Help in DAX Commands and Tips

DAX Formula Help

Re: DAX Formula Help