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

[hohoow_13]

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!

[FreemanZ]

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]

@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

[hohoow_13]

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]

Have a look at https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/