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

Don'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.

Reply
hohoow_13
Regular Visitor

Need help (Power BI) - Dynamic aggregation based on percentage of total that changes with slicer

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:

MonthLocationCategoryCustomerSales
JanAElectronicsC12000
JanAElectronicsC2500
JanAToysC1100
JanAClothingC1200
JanBElectronicsC11000
JanBToysC25000
JanBToysC14000
JanBToysC13000
FebAClothingC5200
FebAToysC4500
FebBGamesC12500
FebBToysC2600
FebBGamesC22500
FebBElectronicsC12000
FebBGamesC33000

 

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:

CategoryCountCustomersTotalSales%ofTotalSales
Games380000.528053
Electronics255000.363036
Others416500.108911

 

Case2 - Month-Jan, Location-A:

CategoryCountCustomersTotalSales%ofTotalSales
Electronics225000.892857
Others13000.107143

 

Case3 - Month-Feb, Location-B:

CategoryCountCustomersTotalSales%ofTotalSales
Games380000.784314
Electronics120000.196078
Others12000.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!

4 REPLIES 4
FreemanZ
Super User
Super User

hi @hohoow_13 ,

 

I don't see the possibility to change the Category info (like adding a "Others" category here) dynamically based on visual actions(like slicer selection here). 
 
We can write new calculated column or calculated table to introduce new "Others" category, but both are not responsive to visual actions (like slicer selection).
Kedar_Pande
Super User
Super User

@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").

johnt75
Super User
Super User

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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