Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

ABC Classification based on a column in Choropleth MAP

Hi Everyone,

 

I am using the Choropleth MAP of India Country. As, I have three levels of data i.e. State, District, and PIN. I want to implement the ABC classification(where [column] >= 80 then "Very High", [column] > 60, then "High", [column] >20 then "Medium", [column] < 20 then "Low").

There are the following conditions -

1. If the user is on State level, then it should show the classification on the basis of the state. 

2.  If the user is on the District level, then it should show the classification on the basis of the district. 

3.  If the user is on the PIN level, then it should show the classification on the basis of the PIN. 

 

Currently, It is working fine for State level only.

 

So, if anyone has any idea, please suggest.

Need Help
Tips and Tricks

2 Replies

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    This is a good example of where setting some VARs in your measure will help.

     

    First create a measure that returns the the users level. Probably that measure will have a couple of nested IF() functions. This measure might be:

    User_Level = 

    VAR state = SELECTEDVALUE(Map[State])

    VAR district = SELECTEDVALUE(Map[District])

    VAR PIN = SELECTEDVALUE(Map[PIN])

    RETURN IF(NOT(ISBLANK(state)),"State",IF(NOT (ISBLANK(district)),"District","PIN")

     

    Then in your measure to calculate the ABC categories can call that measure

    VAR amount= SWITCH([FindUserAmount],"State",MAP[State Value],"District",MAP[District Value],"PIN",MAP[PIN Value])

    RETURN IF(amount >= 80, "Very High", IF(amount > 60, "High", IF(amount > 20,"Medium","Low")))

    I made up some field names and wrote the code without create a power bi file to check it in. So hopefully it will just be enough to give and idea of how to get started. If you need more help with the DAX please post a small sample power bi file and I'll send back a working example.

     

    I'm a personal Power Bi Trainer I learn something every time I answer a question

    The Golden Rules for Power BI

    1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
    2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
    3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
    4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

     

    Tough to provide a solution without sample data and expected output. That being said, see if this quick measure for Dynamic ABC classification helps:

    https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146?search-action-id=97493686267&search-result-uid=479146