Subtotal Problem

DalPai

New Member
Joined
Aug 13, 2018
Messages
29
Hello all!


I am trying to get the total sum of a table, which has values in different currencies (ARS, CLP and UYU).
I started studying DAX last week and I was able to get the correct sum per month, but the subtotals are not working. :(


Basically...

I have a table with the conversion of each currency to dollar:
dgkJuIg.png


To get only ARS values, I used:
Code:
[COLOR=#8B8B8B][FONT=Menlo]Total_Orçado_ARS = SUMX(FILTER(Arg_Orcado;Arg_Orcado[Currency]="ARS");Arg_Orcado[ValorOrçado])[/FONT][/COLOR]
vJef80W.png


Convert ARS to USD:
Code:
[COLOR=#8B8B8B][FONT=Menlo]Convert_ARS_USD = SUMX(FILTER(db_Currency;db_Currency[LocalCurrency]="ARS");db_Currency[Factor])[/FONT][/COLOR]

And to get the final value:
Code:
[COLOR=#8B8B8B][FONT=Menlo]Total_ARS_USD = DIVIDE([Total_Orçado_ARS];[Convert_ARS_USD];0)[/FONT][/COLOR]

The problem: the result per line appears correct, but the subtotal does not.
5sU7bwc.png


1) How do I get the correct subtotal (USD 8,215,067.05)?
2) Seizing the opportunity ... What can I optimize this conversion?


Can someone help me?

Example: https://drive.google.com/file/d/1qS0HxqA_ev6tGogejVhxMSwdVJf_Uory/view?usp=sharing
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top