Forum Discussion

villa1980's avatar
villa1980
Resolver II
1 year ago
Solved

Depot count by group

Hi all,

 I have a list of depots with their capacity. what I would like to do is create a measure to classify these Capcacity (ie, if >1 then ">100%", if >0.8  and <0.9 then "80-90%".
Then once those groups created, I would like to Count those centres within the groups...which should look like

Capacity Group        Count of Depots
>100                            8
80-90                          15

When I have created this on my own the counts work ok when the depots are in the table but when I remove them the groups only show 1 grouping without a count.
These are my DAX for the table...

Capacity_Measure_Groups = SWITCH (
     TRUE(),
    [Capacity_Used] > 1 , ">100%",
    AND([Capacity_Used] >= 0.9 , [Capacity_Used] <1) , "90-100%",
    AND([Capacity_Used] >= 0.8 , [Capacity_Used] <0.9) , "80-90%",
    AND([Capacity_Used] >= 0.7 , [Capacity_Used] <0.8) , "70-80%",
    AND([Capacity_Used] >= 0.6 , [Capacity_Used] <0.7) , "60-70%",
    AND([Capacity_Used] >= 0.5 , [Capacity_Used] <0.6) , "50-60%",
    "<50%"
)
Depot_Capacity_Count = SWITCH(
    TRUE(),
    [Capacity_Measure_Groups] = ">100%" , DISTINCTCOUNTNOBLANK(HOO_AOM[Depot Name]) ,
    [Capacity_Measure_Groups] = "70-80%" , DISTINCTCOUNTNOBLANK(HOO_AOM[Depot Name]) ,
    0
)
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    villa1980  , First Create a calculated column  

    Capacity_Measure_Groups =
    SWITCH (
    TRUE(),
    [Capacity_Used] > 1 , ">100%",
    [Capacity_Used] >= 0.9 , "90-100%",
    [Capacity_Used] >= 0.8 , "80-90%",
    [Capacity_Used] >= 0.7 , "70-80%",
    [Capacity_Used] >= 0.6 , "60-70%",
    [Capacity_Used] >= 0.5 , "50-60%",
    "<50%"
    )

     

    Then create a measure for count

    Depot_Capacity_Count =
    COUNTROWS (
    VALUES ( HOO_AOM[Depot Name] )
    )

     

     

     

    In your table visual, use the Capacity_Measure_Groups column and the Depot_Capacity_Count measure. This way, the groups will be correctly displayed along with their counts, even when the depots are not shown in the table.

     

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    villa1980  , First Create a calculated column  

    Capacity_Measure_Groups =
    SWITCH (
    TRUE(),
    [Capacity_Used] > 1 , ">100%",
    [Capacity_Used] >= 0.9 , "90-100%",
    [Capacity_Used] >= 0.8 , "80-90%",
    [Capacity_Used] >= 0.7 , "70-80%",
    [Capacity_Used] >= 0.6 , "60-70%",
    [Capacity_Used] >= 0.5 , "50-60%",
    "<50%"
    )

     

    Then create a measure for count

    Depot_Capacity_Count =
    COUNTROWS (
    VALUES ( HOO_AOM[Depot Name] )
    )

     

     

     

    In your table visual, use the Capacity_Measure_Groups column and the Depot_Capacity_Count measure. This way, the groups will be correctly displayed along with their counts, even when the depots are not shown in the table.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi villa1980 

     

    Did bhanu_gautam 's method solve your problem? If yes, could you please mark it as a solution? This will be of great help to other users experiencing similar problems. Thank you!

    If you still have questions, please feel free to ask me.

     

    Best Regards,
    Yulia Xu