DAX Measure that don't get sliced based on a Table Column

Anonymous — Mon, 14 Jun 2021 02:40:18 GMT

Lets say we have Product table:

Productid	Product Type	Product Name	Total Audience
1	ABC	aaaa	300
2	ABC	bbbb	700
3	XYZ	cccc	500
4	MNO	dddd	1500

And another table Audience:

AudienceID	email	ProductID	Date	Active	daily_Activity
1	zzz@yx.com	1	11/3/3021	1	4
2	qwe@hg.com	4	11/3/2021	1	6
3	feg@gr.ces	3	12/3/2021	0	0
4	zzz@yx.com	1	12/3/2021	1	3

In PowerBI:

We have Slicers for Product Type, Product Name, Date

we need to create a table like:

Product Name	Product Type	Active Audience %	<- Calculation	Avg Selected Active Audience %	<- Calculation
aaaa	ABC	50%	150/300	70%	700/1000
bbbb	ABC	79%	550/700	70%	700/1000
cccc	XYZ	90%	450/500	90%	450/500

Now if I am only selecting 'aaaa' Product Name from Slicer then the table should be like:

Product Name	Product Type	Active Audience %	<- Calculation	Avg Selected Active Audience %	<- Calculation
aaaa	ABC	50%	150/300	50%	150/300

Note: if say we have 10 products in ABC category and from Slicer if we select 6 products then Avg Selected Active Audience % should be calculated based on 6 products.

I got the DAX for Active Audience %

DAX for Active Audience = CALCULATE(DISTINCTCOUNT('Audience'[email])/SUM('Product'[Total Audience]), FILTER('Product', 'Product'[Product Name] IN ALLSELECTED('Product'[Product Name])))

But I am not sure how to calculate the measure for "Avg Selected Active Audience %", the idea is it shows the % by clubing all the Active Audience for a given product type and it only considers Product Names as selected by slicers.

Current Issue I am facing: The value of Avg Selected Active Audience % comes same as Active Audience % as the data is getting sliced based on table row; If I show the measure in KPI card for Avg Selected Active Audience % it shows correct value.

Could anyone help me figure out the correct DAX for Avg Selected Active Audience %.

Re: DAX Measure that don't get sliced based on a Table Column

lbendlin — Mon, 16 Aug 2021 23:18:04 GMT

Anonymous first let's assume that you have linked the tables in the data model via the ProductID

Your measure for Active Audience = CALCULATE(DISTINCTCOUNT('Audience'[email])/SUM('Product'[Total Audience]), FILTER('Product', 'Product'[Product Name] IN ALLSELECTED('Product'[Product Name]))) is not correct for product aaaa as that has only one dedicated email address associated.

Now we get some result by simply counting the emails (without distinct)

This is where it breaks down. Your sample data does not support the 79% value for bbbb. Can you please explain that part?

topic Re: DAX Measure that don't get sliced based on a Table Column in DAX Commands and Tips

DAX Measure that don't get sliced based on a Table Column

Re: DAX Measure that don't get sliced based on a Table Column