Cumulative rate by group in chart

[PaulShlapa]

Dear Experts,

I have a dataset that consists of two tables

CurrentCurrencyRates

InflationRates

I would like to use CurrentCurrencyRates as a filter table and obtain cumulative inflation rates over time for country and currency

I have a measure for cumulative inflation rates

Cumulative Inflation Rate = 
CALCULATE (
    SUM ( 'InflationRates'[Shifted Inflation Rate] ),
    ALLEXCEPT(  'InflationRates', InflationRates[CurrencyType] ),
    'InflationRates'[Year] <= MAX ( InflationRates[Year] )
)

The end result should look like this:

1) when Currency EUR is chosen

2) when Currency ARS is chosen

Filters SalesCompany and Currency come from the first table CurrentCurrencyRates and it should stay like this if possible.

At the moment however Cumulative Inflation Rate measure does react to Currency filter and the result is the same no matter which Currency i choose. It looks like it cumulates Shifted Inflation Rate irrespective of the Currency Type.

How could i improve on this?

Any help and suggestions are very appreciated.

Link to the PBIX File:

https://www.dropbox.com/scl/fi/mje5qgya6hiamcd85qszy/CumulativeCurrencyRatesTest.pbix?rlkey=y8d8q4ma...

Sincerely,

Pavlo

[v-xiandat-msft · Accepted solution]

Hi @PaulShlapa ,

Below is my table1:

Below is my table2:

Since these two tables have a many-to-many relationship, a third bridged table needs to be created:

Then the relationship is like this:

The following DAX might work for you:

Cumulative Inflation Rate = 
// CALCULATE (
//     SUMX( InflationRates , 'InflationRates'[Shifted Inflation Rate] ),
//     // ALLEXCEPT(  'InflationRates', InflationRates[CurrencyType] ),
//     'InflationRates'[Year] <= MAX ( InflationRates[Year] )
// )
CALCULATE (
    SUM ( 'InflationRates'[Shifted Inflation Rate] ),
    FILTER(
        ALL('InflationRates'),
        'InflationRates'[Year] <= MAX ( 'InflationRates'[Year] )
        && 'InflationRates'[CurrencyType] IN VALUES ( 'CurrentCurrencyRates'[CurrencyType] )
    )
)

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulShlapa]

Very impressive @v-xiandat-msft work perfectly, many thanks

[PaulShlapa]

Thanks @v-xiandat-msft , a good approach, still it does not filter LC for another Sales Company AE, which has which has LC AED. I guess your solution with a bridge table does not consider that.

[v-xiandat-msft · Accepted solution]

Hi @PaulShlapa ,

My LC is like this:

The updated bridge table:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xiandat-msft · Accepted solution]

Hi @PaulShlapa ,

Below is my table1:

Below is my table2:

Since these two tables have a many-to-many relationship, a third bridged table needs to be created:

Then the relationship is like this:

The following DAX might work for you:

Cumulative Inflation Rate = 
// CALCULATE (
//     SUMX( InflationRates , 'InflationRates'[Shifted Inflation Rate] ),
//     // ALLEXCEPT(  'InflationRates', InflationRates[CurrencyType] ),
//     'InflationRates'[Year] <= MAX ( InflationRates[Year] )
// )
CALCULATE (
    SUM ( 'InflationRates'[Shifted Inflation Rate] ),
    FILTER(
        ALL('InflationRates'),
        'InflationRates'[Year] <= MAX ( 'InflationRates'[Year] )
        && 'InflationRates'[CurrencyType] IN VALUES ( 'CurrentCurrencyRates'[CurrencyType] )
    )
)

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.