Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Bucketing based on measure value

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 returnedResult returned
Expected result setExpected 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
GROUP BY MONTH_KEY,GROUP

 

3 REPLIES 3
Anonymous
Not applicable

@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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.