Bucketing based on measure value

[Anonymous]

Hi All,

I am trying to create buckets based on Sales aggregation and the total count I get is correct but the count for each bucket is wrong not sure why it's happening, below are my measures:

1. Sales = SUM(vw_PBI_FACT_Sales_MONTHLY[Sales])      Here, also tried using ALLEXCEPT(PARENT)

2. Parent Count =  DISTINCTCOUNT(vw_PBI_FACT_USAGE_MONTHLY[PARENT])

3. Buckets = SWITCH (
    TRUE (),
    [Sales] > 0
        && [Sales] <= 10, "   0-10",
    [Sales] >= 11
        && [Sales] <= 50, "  11-50",
    [Sales] >= 51
        && [Sales] <= 100, "  51-100",
    [Sales] >= 101
        && [Sales] <= 500, " 101-500",
    [Sales] >= 501
        && [Sales] <= 1000, " 501-1000",
    [Sales] >= 1001, "1000+"
)

Result returned
Expected result set

Note: I have several slicers on the page including "Parent".

Below is the SQL Query:

WITH A (MONTH_KEY,PARENT,GROUP,SALES)
AS
(SELECT MONTH_KEY,PARENT_NAME,
CASE
WHEN SUM(SALES) BETWEEN 0 and 10 THEN 'A) 1-10'
WHEN SUM(SALES) BETWEEN 11 and 50 THEN 'B) 11-50'
WHEN SUM(SALES) BETWEEN 51 and 100 THEN 'C) 51-100'
WHEN SUM(SALES) BETWEEN 101 and 500 THEN 'D) 101-500'
WHEN SUM(SALES) BETWEEN 501 and 1000 THEN 'E) 501-1000'
ELSE 'F) 1000+' END GROUP,
SUM(SALES) SALES 
FROM vw_PBI_FACT_SALES_MONTHLY
GROUP BY MONTH_KEY,PARENT 
HAVING MONTH_KEY > 201700 and SUM(SALES) > 0)

SELECT MONTH_KEY,GROUP,COUNT(DISTINCT PARENT) PARENT_COUNT
FROM A 
GROUP BY MONTH_KEY,GROUP

[Anonymous]

@Anonymous can you post the power bi file so I can see what you are doing in there. The formulas seem right in general but need to see the context how they are used.

[Anonymous]

Hi @Anonymous,

Thanks for the response. The data in the Power BI file is sensitive and I am afraid I can't share it with the community. Sorry about that and please share any alternative measures that would fix my issue.

Thanks

[Anonymous]

@Anonymous i would suggest creating a dummy data sheet and recreating the issue and posting that instead.