count by buckets

[jostnachs]

Hi all....

I have a requirement where i have to show count of clients grouped by commission buckets.

But as per below screenshots there is only 1 client but he is falling under different buckets hence showing client count as 3. but it should be 1. how do i do that?

formula for commission_buckets 

# clients 

[danextian · Accepted solution]

Hi @jostnachs 

Apply your conditional column to the total estimated compensasion by client and not to the individual commission rows.

Commission_Bucket =
VAR ClientCommission =
    CALCULATE (
        SUM ( V_DimPolicy[EstimatedCommission] ),
        ALLEXCEPT ( V_DimPolicy, V_DimPolicy[ClientID] )
    )
RETURN
    SWITCH (
        TRUE (),
        ClientCommission >= 0
            && ClientCommission <= 5000, "<5K",
        ClientCommission > 5000
            && ClientCommission <= 10000, "5K-10K",
        ClientCommission > 10000
            && ClientCommission <= 30000, "10K-30K",
        ClientCommission > 30000
            && ClientCommission <= 50000, "30K-50K",
        ClientCommission > 50000
            && ClientCommission <= 100000, "50-100K",
        ClientCommission > 100000, "100K+",
        ISBLANK ( ClientCommission ), "Blank",
        "Out of Range" -- Optional to catch any edge cases
    )

[jostnachs]

Perfect! Worked like a charm... thanks a lot @danextian 

[Ritaf1983]

Hi @jostnachs 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523