Forum Discussion

Pbiuserr's avatar
Pbiuserr
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Get a category name with minimum value

Hello, 

The measure down below shows how to get category name with maximum value

 

CALCULATE(
MAX(Fct_Table[Category]),
TOPN(1, FILTER(
SUMMARIZE ( Fct_Table, Fct_Table[Gender], Fct_Table[Category],
"@HC", [Change]), Fct_Table[Gender] = "Female"),
[Change])
)

however I wanted to retrive MIN value. Changed MAX(Fct_Table[Category]) to MIN(Fct_Table[Category]), tried to make TOPN1 DESC/ASC nothing works
 
Any suggestions?
Need Help

12 Replies

  • krohitrao's avatar
    krohitrao
    Regular Visitor
    4 years ago

    1. Create a measure for getting max of the change:
        MaxChange = CALCULATE(MAX(Fct_Table[Change]), ALL(Fact_Table))
    2. Create another measure to get the category with max change:
        MaxCategory = CALCULATE(MAX(Fct_Table[Category]), FILTER(Fact_Table, Fct_Table[Change] = [MaxChange]))

    Same goes with finding minimum category, create another set of measures and replace MAX with MIN

    • Pbiuserr's avatar
      Pbiuserr
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Yes but the table in this scenario must consist Fct_Table[Gender] and Fct_Table[Category] and its need to be sorted by [Change] measure. Can you help me build such measure?


      Edit: Change is not a Fct_Table[Change], its a measure

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hello Pbiuserr ,

    Could you please share sample data in text format along with expected output if possible?

    Kind regards,

    Rohit

    • Pbiuserr's avatar
      Pbiuserr
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Hey,
      Sory, data is confidential unfortunately

      Expected result is:

      You have a table like (I've made it inside the measure)

      GenderCategoryChange
      MaleCategory2 10%
      FemaleCategory1 2%
      FemaleCategory5 3%
      MaleCategory3 7%

      from that I want retrive name of Category1 as its MIN [Change] (2%). The measure I've put will retrived Category2 as its MAX [Change] (10%). I can make it for MAX but can't for MIN and seek some help

      • rohit_singh's avatar
        rohit_singh
        Icon for Solution Sage rankSolution Sage
        4 years ago

        Hi Pbiuserr ,

        Please try creating the following measures :

        Min Change =

        var _min =
        CALCULATE(
        MIN(Gender[Change]),
        FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female"
        )
        )

        var _category =
        CALCULATE(
        VALUES(Gender[Category]),
        FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female" && Gender[Change] = _min
        )
        )
        RETURN
        _category

        Max Change =

        var _max =
        CALCULATE(
        MAX(Gender[Change]),
        FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female"
        )
        )

        var _category =
        CALCULATE(
        VALUES(Gender[Category]),
        FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female" && Gender[Change] = _max
        )
        )
        RETURN
        _category
         
        Final output

         

        Kind regards,

        Rohit


        Please mark this answer as the solution if it resolves your issue.
        Appreciate your kudos! ğŸ˜Š