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
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.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors