Multiplication Measure and Column issue

Hello,

I am running into a situation where I want to multiply a column with a measure.

Little background:

  1. I have data in local currency. I am multiplying it by a conversion factor. I get this conversion factor based off of the latest value of the slicer selected. Thanks @Harsh for helping me with that.

  2. Now when I put the conversion exchange rate in the table then it calculates it correctly as in Table 1.

  3. But if I remove the Conversion Exchange Rate from the table then I get very big value. I understand it is multiplying based on this formula:

AVG - Jan 1st EE Avg. Salary DOLLARS M = 
'Wage Inflation Measures'[AVG - Jan 1st EE Avg. Salary Loc. Currency M]*HRIS_MD_EXCHANGE_RATES[Conversion Exchange Rate M]
  1. How do I force it to use the single conversion rate (in this case .2596) without having that in the table?

pbix: https://drive.google.com/file/d/1Gc1JXF_AVR9SeCZk7CgXMQUurHx1CWB6/view?usp=share_link

Thanks,
EZ

Hello @rit372002,

Thank You for posting your query onto the Forum.

You’re using a naked field/column called “Conversion Exchange Rates” directly from the “Exchange Rates” table and then converting it into a “Don’t Summarize”. And therefore, when you remove that field from the table, it also removes that temporary context which was created.

Also it’s observed that inside your table visual as well as in your slicers, you’re calling out the fields directly from the Fact Table (which is not recommended approach since they ideally should come from Dimension Table) so measure needs to be created in accordance with that. Below is the DAX measure alongwith the screenshot of the final results provided for the reference -

Exc. Rate = 
CALCULATE( SUM( HRIS_MD_EXCHANGE_RATES[Conversion Exchange Rate] ) , 
    CROSSFILTER( HRIS_TD_COMP_WAGE_DETAIL_NEWOLD[Distinct Year Month Currency] , HRIS_MD_EXCHANGE_RATES[DistinctYearmonthCurrency ] , Both ) )
Total Conversion (in $) = 
[AVG - Jan 1st EE Avg. Salary Loc. Currency M] * [Exc. Rate]

As you can observe in the above provided screenshot, that although the measure of “Exc. Rate” is not included inside the second table visual, it still multiplies and evaluates the results correctly when compared with the first table visual.

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Enterprise - Conversion - Harsh.pbix (8.9 MB)

1 Like

Thanks @Harsh, it was brilliant, Unfortunately, there are few issues. As I couldn’t explain the whole thing earlier. Apologies…

  1. I have to select multiple months and only show the latest Exchange Rate for that month. In this example, I select till Apr. It is showing Exchange rate for that month which is good and the last column is good too. So it is converting it correctly.

Issue #1: Users only want to see 2nd table the Manager Level and data in Dollars, which is second table.

As I chose multiple months, the crossfilter formula you created didn’t work.

Exc. Rate = 
CALCULATE( SUM( HRIS_MD_EXCHANGE_RATES[Conversion Exchange Rate] ) , 
    CROSSFILTER( HRIS_TD_COMP_WAGE_DETAIL_NEWOLD[Distinct Year Month Currency] , HRIS_MD_EXCHANGE_RATES[DistinctYearmonthCurrency ] , Both ) )

I am looking for something like this : a pivot table for above scenario, with grand total being 1743198.

image

Issue # 2: The total is incorrect in the first table. No one, will be looking at it but I am just curious, how to get it right?

pbix: https://drive.google.com/file/d/1zAY0UeG3ESVpB6WBwkKbinJeLfiE6TfD/view?usp=share_link

pivot table output: https://drive.google.com/file/d/1Ub9f8NGT3_GMbAcME8joKNR_xHLhUqiB/view?usp=share_link

Please note: Conversion Exchange Rates are different in the first table as there are multiple currencies. just fyi.

Again thanks a lot for your brilliant solutions…looking forward to this one.
-EZ

Hello @rit372002,

Based on the scenario that you had specified in the original post alongwith the screenshot, solution was provided in accordance with that wherein you wanted to multiply the single conversion rate with the average figures.

For the new scenario’s, as per the forum guidelines, please create a separate thread by providing the proper description of the query.

And as far as, totalling is concerned, below is the link of the thread provided for the reference which addresses the issue pertaining to the topic - “Fix Incorrect Totals”.

Note: In your revised query, you haven’t specified which all currencies are considered for evaluation of results. Are you considering multiple latest currencies or exchange rates at a given time. Why? Please provide proper description in your new thread so that members of the forum can assist you in a better and efficient manner.

Thanks and Warm Regards,
Harsh

@Harsh : Thanks! I will create a new thread. Thanks for asking excellent question.

Regarding your question: Are you considering multiple latest currencies or exchange rates at a given time. Why?

The reason is: Ultimately they want to know the salary in dollars for people in same position in different part of their organization (which could be in multiple countries). So they want to know the exchange rate at the latest month selected for that currency. I hope that answers your question.

Can’t thank you enough as I am learning a lot!

-EZ