Forum Discussion

prof_hoo's avatar
prof_hoo
Frequent Visitor
4 years ago
Solved

Find row value based on two other columns

I want to find the type based on a numeric column for each state. Here is an brief example of the data I have. I have a location column (State), a category column (Type), and a value column. For each...
Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    prof_hoo Try this measure, change table and column name as per your model

     

    Top Type = 
CALCULATE ( 
    MAX ( HighType[Type] ),
    TOPN (
        1, 
        ALLSELECTED ( HighType[Type] ),
        CALCULATE ( SUM ( HighType[Value] ) ),
        DESC
    )
)

     

    Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

prof_hoo now it makes a bit of sense, but why 'Y' for all, what is the logic behind that, or if more than one state is selected it will be 'Y'

 

 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

 

prof_hoo's avatar
prof_hoo
Frequent Visitor
4 years ago

Because if you sum up all 4 types, W = 6, X = 6, Y = 8, and Z = 7, so overall, Y is the the type with the highest value.