Forum Discussion

tnaess's avatar
tnaess
New Member
2 years ago

Measure with top 3

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?

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      tnaess
      New Member
      2 years ago

      Hi Anonymous ,

       

      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.