Convert Measure to calculation group

[Anonymous]

Hi all,

I´ve this measures that convert sales currency (multiple currencies) to USD, EUR and GBP currency selected in a report slicer:

CreationForex = IF ( ISFILTERED ( CreationForex[CurrencyTo] ), VAR ForexRate = CALCULATE ( FIRSTNONBLANK ( CreationForex[Rate], 1 ), FILTER ( CreationForex, CreationForex[Currency] = SELECTEDVALUE ( Sales[SaleCurrency] ) && CreationForex[CurrencyTo] = SELECTEDVALUE ( CreationForex[CurrencyTo] ) ) ) VAR CurrencyFormat = SELECTEDVALUE ( CreationForex[CurrencyCharacter] ) VAR NetSalePrice = [SumNetSalePrice] VAR Result = CONCATENATE ( CurrencyFormat, FORMAT ( DIVIDE ( NetSalePrice, ForexRate, 0 ), "0.00" ) ) RETURN Result, [SumNetSalePrice] )

I have [SumNetSalePrice] = SUM (NetSalePrice) and wanted to create more measures, all of them sumatories of diferents columns.

Then using them in a table like:

And change the value of this measures using the slicer.

Now is  working correctly but of course only with the measure hardcoded in the CreationForex measure.

I´ve tryied to convert the CreationForex to a calculation group:

CreationForex = IF ( ISFILTERED ( CreationForex[CurrencyTo] ), VAR ForexRate = CALCULATE ( FIRSTNONBLANK ( CreationForex[Rate], 1 ), FILTER ( CreationForex, CreationForex[Currency] = SELECTEDVALUE ( Bookings[SaleCurrency] ) && CreationForex[CurrencyTo] = SELECTEDVALUE ( CreationForex[CurrencyTo] ) ) ) VAR CurrencyFormat = SELECTEDVALUE ( CreationForex[CurrencyCharacter] ) VAR NetSalePrice = SELECTEDMEASURE () VAR Result = CONCATENATE ( CurrencyFormat, FORMAT ( DIVIDE ( NetSalePrice, ForexRate, 0 ), "0.00" ) ) RETURN Result, SELECTEDMEASURE () )

I get an error from Tabular editor and Power Bi, I'm facing this wrongly? Any idea how can be this achieve?

I'll try to test calculation groups with easiest examples, but if any master user 😉 can tell me id this kind of things can be done, I´ll own him/her a drink!

Thanks in advance for your time.

[Anonymous]

I think this code is more efficient:

CreationForex =
// You should calculate things
// only one time for the sake of speed.
VAR Measure_ = SELECTEDMEASURE() // [SumNetSalePrice]
RETURN
IF( HASONEFILTER( CreationForex[CurrencyTo] ),
    
    VAR SaleCurrency =
        SELECTEDVALUE( Sales[SaleCurrency] )
    VAR ForexRate =
        CALCULATE(
            SELECTEDVALUE( CreationForex[Rate] ),
            CreationForex[Currency] = SaleCurrency
        )
    VAR CurrencyFormat =
        SELECTEDVALUE( CreationForex[CurrencyCharacter] )
    VAR Result =
        CONCATENATE(
            CurrencyFormat,
            FORMAT(
                DIVIDE(
                    Measure_,
                    ForexRate,
                    0
                ),
                "0.00"
            )
        )
    RETURN
        Result,

    Measure_
)

I'm not sure if calc groups work exactly the same in Power BI as the do in SSAS but the item above returns 2 different data types: string and a number. This is suboptimal, to say the least. This item should always return a number. I know that in Tabular Editor you can define the format string dynamically without affecting the value of the calculation. Please use this feature to set formatting correctly and remove formatting from the item.