Measure with top 3

[tnaess]

Hi all. I have a few tables with data and I'm sharing some examples of what they contains.

This table contains general ledger transactions.

This table contains the main accounts with its transaction type. Here the accouning year is added since the transaction type on an account can change from year to year.

This table contains financial dimension values for the dimension 'Motpart'

The model view is like this:

Please note that the data above are only example data which doesn't add up in total.

I've created a simple report with table layout where I show the following columns:

- Name --> This is the legal entity name

- Controlled transactions --> This is the "Controlled transactions" from the MainAccountMapping table

- SUM --> This is a measure, showing the formula below

CONTROLLED TRANSACTIONS TOTAL =

CALCULATE(

    SUM('GJAE Trans'[REPORTINGCURRENCYAMOUNT]) * -1,

    FILTER(

        ALLSELECTED('MainAccountMapping'[Controlled transactions]),

        ISONORAFTER('MainAccountMapping'[Controlled transactions], MAX('MainAccountMapping'[Controlled transactions]), DESC)

    )

)

All this is obviously quite simple and is working as expected. What I want is a new column that is showing the country code from the table "MotpartSettings" for the top 3 motpart for the calculated sum on the report. Something like this:

Any ideas on how to solve this?

[v-cgao-msft]

Hi @tnaess ,

Please try this measure:

Country Code = CONCATENATEX(TOPN(3,CALCULATETABLE(VALUES('MotpartSettings'[COUNTRYCODE])),[CONTROLLED TRANSACTIONS TOTAL],DESC),'MotpartSettings'[COUNTRYCODE],"|")

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[tnaess]

Hi @v-cgao-msft ,

thanks a lot. Just one additional question, how do I adjust the number of contry codes to be the same as the top3? At the moment it lists more than three values.