Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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+"
)
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 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.
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 i would suggest creating a dummy data sheet and recreating the issue and posting that instead.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |