Forum Discussion
count by buckets
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
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 )
3 Replies
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/1447523Hi 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 )
