Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I'm having some trouble writing DAX to produce the visual table that I need in Power BI.
I have a data table loaded, with columns Month, Location, Category, Customer and Sales. The table is as shown below:
Month | Location | Category | Customer | Sales |
Jan | A | Electronics | C1 | 2000 |
Jan | A | Electronics | C2 | 500 |
Jan | A | Toys | C1 | 100 |
Jan | A | Clothing | C1 | 200 |
Jan | B | Electronics | C1 | 1000 |
Jan | B | Toys | C2 | 5000 |
Jan | B | Toys | C1 | 4000 |
Jan | B | Toys | C1 | 3000 |
Feb | A | Clothing | C5 | 200 |
Feb | A | Toys | C4 | 500 |
Feb | B | Games | C1 | 2500 |
Feb | B | Toys | C2 | 600 |
Feb | B | Games | C2 | 2500 |
Feb | B | Electronics | C1 | 2000 |
Feb | B | Games | C3 | 3000 |
I need to create a table visual, with 4 columns (Category, DistinctCount of Customers , Total Sales, and Percentage of Total Sales). My requirements are as follows:
1) The visual will need to be dynamic according to the slicers and filters selected. In this case, Month and Location are used as slicers.
2) For Category, if the percentage of total sales for that category is less than 10%, then that category would be grouped as "Others", and eventually aggregated with the other "Others" in the visual.
From the data above, I expect to see the following visual when the slicers are selected:
Case1 - No slicers:
Category | CountCustomers | TotalSales | %ofTotalSales |
Games | 3 | 8000 | 0.528053 |
Electronics | 2 | 5500 | 0.363036 |
Others | 4 | 1650 | 0.108911 |
Case2 - Month-Jan, Location-A:
Category | CountCustomers | TotalSales | %ofTotalSales |
Electronics | 2 | 2500 | 0.892857 |
Others | 1 | 300 | 0.107143 |
Case3 - Month-Feb, Location-B:
Category | CountCustomers | TotalSales | %ofTotalSales |
Games | 3 | 8000 | 0.784314 |
Electronics | 1 | 2000 | 0.196078 |
Others | 1 | 200 | 0.019608 |
I first computed totalsales and countcustomers by using sum(sales) and distinctcount(customers), and %oftotalsales as totalsales/calculate(sum(sales), allselected(category).
I then tried creating a calculated measure for "Category" as:
if(%oftotalsales < 0.1, "Others", selectedvalue(category).
However, this doesnt seem to be correct as the requirement of "others" does not seem to work without the original category context (i.e. I need to insert the original Category into the visual for the "Others" to show up, but causing "Others" to not aggregate in this case).
Any help is appreciated, thanks much!
hi @hohoow_13 ,
Create measures:
TotalSales = SUM('SalesData'[Sales])
PctOfTotalSales =
DIVIDE(
[TotalSales],
CALCULATE(SUM('SalesData'[Sales]), ALLSELECTED('SalesData'[Category]))
)
CountCustomers = DISTINCTCOUNT('SalesData'[Customer])
DynamicCategory =
VAR CurrentCategory = SELECTEDVALUE('SalesData'[Category])
VAR CategorySales = [TotalSales]
VAR TotalSalesAllCategories = CALCULATE(SUM('SalesData'[Sales]), ALLSELECTED('SalesData'[Category]))
VAR CategoryPct = DIVIDE(CategorySales, TotalSalesAllCategories)
RETURN
IF(CategoryPct < 0.1, "Others", CurrentCategory)
TotalSalesGrouped =
SUMX(
SUMMARIZE(
'SalesData',
'SalesData'[DynamicCategory],
"SalesSum", [TotalSales]
),
[SalesSum]
)
CountCustomersGrouped =
SUMX(
SUMMARIZE(
'SalesData',
'SalesData'[DynamicCategory],
"CustomerCount", [CountCustomers]
),
[CustomerCount]
)
PctOfTotalSalesGrouped =
DIVIDE(
[TotalSalesGrouped],
CALCULATE(SUM('SalesData'[Sales]), ALLSELECTED())
)
Add DynamicCategory as the first column in your visual.
Use the following measures in the other columns:
Distinct Count of Customers → [CountCustomersGrouped]
Total Sales → [TotalSalesGrouped]
% of Total Sales → [PctOfTotalSalesGrouped]
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Hi @Kedar_Pande thanks for your help!
Unfortunately I tried the DAXes you've written but run into the following problemes:
1) There is no 'SalesData['Dynamic Category'] if I've written DynamicCategory as a measure. In which case I have written DynamicCategory as a calculated column instead using your same DAX.
2) This DynamicCategory col shows nothing when I insert the column into the visual.
3) I've also tried writing a separate measure for DynamicCategory(M), but the same problem exists when I insert this into the visual (only shows value when original Category is inserted into the visual, and does not aggregate with the other "Others").
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |