Forum Discussion

jostnachs's avatar
jostnachs
Helper IV
1 year ago
Solved

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...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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
    )

     

danextian's avatar
danextian
Super User
1 year ago

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's avatar
jostnachs
Helper IV
1 year ago

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