Depot count by group

[villa1980]

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

)

[Anonymous]

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

[bhanu_gautam · Accepted solution]

@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.