Works with Intermediate measures, doesn't work with VAR. Why?

[mede]

I am quite puzzled by how DAX behaves.

I am calculating dynamic sales value that changes based on currency selection.

I have two intermediate measures:

Sales (at row) = SUM(Sales[net_sales_local])*MIN('FX Rates2'[FX])
Sales (at total) = SUMX(SUMMARIZE(Sales,'FX Rates'[Currency]),CALCULATE(VALUES('FX Rates2'[FX]))*SUM(Sales[net_sales_local]))

And the final measure which calculate Sales in the right way for each Pivot Cell:

Sales = IF(COUNTROWS(VALUES('FX Rates2'[FX]))=1,Sales[Sales (at row)],SUMX(VALUES('FX Rates2'[FX]),[Sales (at total)]))

This approach works accurately.

But dont want intermediate measures to appear in my data model, so i tried to create one measure using VAR and get the same result

Sales Ultimate = 
VAR sales_at_row = SUM(Sales[net_sales_local])*MIN('FX Rates2'[FX])
VAR sales_at_total = SUMX(SUMMARIZE(Sales,'FX Rates'[Currency]),CALCULATE(VALUES('FX Rates2'[FX]))*SUM(Sales[net_sales_local]))
RETURN
IF(COUNTROWS(VALUES('FX Rates2'[FX]))=1,sales_at_row,SUMX(VALUES('FX Rates2'[FX]),sales_at_total))

 this does not give the right result at the Subtotals and Grand Total levels.

Why?

[mede · Accepted solution]

Actually, I have solved it with one formula:

Sales Ultimate = IF(COUNTROWS(VALUES('FX Rates2'[FX]))=1,VALUES('FX Rates2'[FX])*SUM(Sales[net_sales_local]),SUMX(SUMMARIZE(Sales,'FX Rates'[Currency]),CALCULATE(VALUES('FX Rates2'[FX])*SUM(Sales[net_sales_local]))))

[Eric_Zhang]

---

@mede wrote:

I am quite puzzled by how DAX behaves.

I am calculating dynamic sales value that changes based on currency selection.

I have two intermediate measures:

Sales (at row) = SUM(Sales[net_sales_local])*MIN('FX Rates2'[FX])
Sales (at total) = SUMX(SUMMARIZE(Sales,'FX Rates'[Currency]),CALCULATE(VALUES('FX Rates2'[FX]))*SUM(Sales[net_sales_local]))

And the final measure which calculate Sales in the right way for each Pivot Cell:

Sales = IF(COUNTROWS(VALUES('FX Rates2'[FX]))=1,Sales[Sales (at row)],SUMX(VALUES('FX Rates2'[FX]),[Sales (at total)]))

This approach works accurately.

But dont want intermediate measures to appear in my data model, so i tried to create one measure using VAR and get the same result

Sales Ultimate = 
VAR sales_at_row = SUM(Sales[net_sales_local])*MIN('FX Rates2'[FX])
VAR sales_at_total = SUMX(SUMMARIZE(Sales,'FX Rates'[Currency]),CALCULATE(VALUES('FX Rates2'[FX]))*SUM(Sales[net_sales_local]))
RETURN
IF(COUNTROWS(VALUES('FX Rates2'[FX]))=1,sales_at_row,SUMX(VALUES('FX Rates2'[FX]),sales_at_total))

 this does not give the right result at the Subtotals and Grand Total levels.

Why?

---

@mede

I guess you don't put those measures in the same place? The same measure's value can vary according to the context(the different places). Please post more details of your scenario for further suggestion.

[mede]

Hi @Eric_Zhang,

Here is the details of my scenario:

• I have a star schema for monthly sales information for 7 countries that uses 5 different currencies.
  • one fact table for Sales & Margin with location, date and product dimensions. (data model is attached below)
• All measures (Sales, Sales LY, Margin, Margin LY) are in local currency.
• Fixed exchange rates are being used for all dates. There is an FX Rate dimension table which basically has the rate to convert from each Local Currency to USD, to NOK and to Local Currency. (columns: Currency, Viz Currency, FX rate)

I'd like to create measures that shows the Sales & Margin for any selected "Viz Currency", so: if Viz Currency Slicer is set to USD, all  [Sales] should be in USD (using the right FX rate for every row to convert), if "Viz Currency" is set to "Local Currency", all [Sales] should be in local currency (using the right FX rate -which is 1 in this case-, all [Sales] should be in Local Currency.

I have managed to do this with the DAX measures mentioned above.

*Viz Currency slicer is a forced, single select chiclet slicer.

Essentially [Sales] in any selected viz currency should be  SUM(Sales[net_sales_local])*MIN('FX Rates2'[FX]) but the key challenge i faced was getting the right result at subtotal and Total levels when i have multiple countries in the pivot. Because the pivot cell will take the MIN (or any other stat) into account while calculating the subtotals and totals - which will be wrong.

What is needed in this context was to use SUMX and somehow calculate the totals with row level iterations to get the correct totals.

Then what i did is I have created two measures "sales at row level" (gives correct result at row level) & "sales at total level" (gives correct total at total levels), and create this ultimate Sales measure which checks: IF the pivot cell gets only 1 FX rate, then return the "sales at row level", ELSE return the "sales at total level".

This all works as long as I create those intermediate measures in the data model, but i don't want those to show up, so i decided to put all of this in one measure using VAR definitions inside the dax measure formula. That's when i found out that it gives wrong result, mainly because first IF always returns true in this context I think.

See the data model below for reference: (i had to create a bridge table between FX Rates2 and Location for avoiding "many to many" relatonship problem.