cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lcfaria
Helper I
Helper I

Calculation Group to convert currency - How ignore for measures that are not currency?

Hi all,

 

I created a simple calculation group to convert my currency from USD to EUR.

 

It is a simple query:

lcfaria_0-1653824776303.png

 

The problem is that I don't want to apply this calculation group to quantity, just to currency measures.

 

I tried to use REMOVEFILTERS, but it didn't work:

CALCULATE(
     SUM(factTable[Qty]),
     REMOVEFILTERS('CalculationGroup'[Convertion])
)
 

Is there any way to not consider this calculation group for some measures?

 

Thanks!

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

You need to consider the filter context.

 

When you apply the calculation group:

SUMX(
    factTable;
    SELECTEDMEASURE() * 0,9
)

 

which evaluates to 

SUMX(
    factTable;
    AVERAGEX(
        VALUES('Calendar'[Date]);
        [Sum Total]
    ) * 0,9
)

 

SUMX iterates over factTable, and for each row it calculates the average of that row. And the average of a single row is the same as the value of that row. So with this calculation group, both [Avg/Day] and [Sum Total] returns the same value.

I can't see any reason why you could not replace the current calculation group 

=SUMX(
    factTable;
    SELECTEDMEASURE() * 0,9
)

with 

=SELECTEDMEASURE() * 0,9

 

And with the latter you will get the correct values with both measures.

View solution in original post

4 REPLIES 4
sturlaws
Resident Rockstar
Resident Rockstar

Hi,

 

have you tried using SELECTEDMEASURENAME() combined with an if-statement?

=
IF (
    SELECTEDMEASURENAME () = "some measure",
    SELECTEDMEASURE (),
    SUMX ( facttable, SELECTEDMEASURE () * 0.9 )
)

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

@sturlawsyour solution worked fine, but I ran into another problem.

 

I have a measure that calculates the average per day, but when I apply the calculation group, the result is not correct. Instead of bringing me the converted average value, this brings me the converted value of the measure [Sum Total].

 

Measures:

lcfaria_0-1653842269213.png

lcfaria_1-1653842278555.png

 

Whitout applying calculation group:

lcfaria_2-1653842343598.png

Applying calcution group:

lcfaria_3-1653842371425.png

 

Do you know how I can get around this?

 

I am attaching the sample file in this link: https://1drv.ms/u/s!At5JlMR_naI1gf4zcSKNi8b27E6Evg?e=eJcULs

sturlaws
Resident Rockstar
Resident Rockstar

You need to consider the filter context.

 

When you apply the calculation group:

SUMX(
    factTable;
    SELECTEDMEASURE() * 0,9
)

 

which evaluates to 

SUMX(
    factTable;
    AVERAGEX(
        VALUES('Calendar'[Date]);
        [Sum Total]
    ) * 0,9
)

 

SUMX iterates over factTable, and for each row it calculates the average of that row. And the average of a single row is the same as the value of that row. So with this calculation group, both [Avg/Day] and [Sum Total] returns the same value.

I can't see any reason why you could not replace the current calculation group 

=SUMX(
    factTable;
    SELECTEDMEASURE() * 0,9
)

with 

=SELECTEDMEASURE() * 0,9

 

And with the latter you will get the correct values with both measures.

Hey @sturlaws you are right, I made this change in the calculation group and it worked as expected.

 

Thank you very much for your attention and explanation on this topic. It helped me clarify things a little more. 👊

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors