topic Re: DAX to calculate dynamically within Selected Group in DAX Commands and Tips

DAX to calculate dynamically within Selected Group

vishwa_sg — Wed, 10 Aug 2022 14:32:07 GMT

Dear Community,

I am very new to power BI. This could be simple requirement but I am struggling to make it work.

Here is my requirement :

DAX to calculate dynamically within group.

Type	EMPLID	Calculation	expected result
CTA	1001	distinct emplid/ count(type)	0.33
Generic	1001	distinct emplid/ count(type)	0.33
Email	1001	distinct emplid/ count(type)	0.33
XYZ	1002	distinct emplid/ count(type)	1.00

I have Type slicer on the report page and when I select ex: Type ->CTA, Generic then expected result is

Type	EMPLID	Calculation	expected result
CTA	1001	distinct emplid/ count(type)	0.50
Generic	1001	distinct emplid/ count(type)	0.50
Email	1001	distinct emplid/ count(type)
XYZ	1002	distinct emplid/ count(type)

Please help.

Thanks,

Re: DAX to calculate dynamically within Selected Group

SpartaBI — Wed, 10 Aug 2022 14:49:00 GMT

vishwa_sg

measure = divide(distinctcount(emplid), calculate(distinctcount(type), allselected(type)))

vishwa_sg Here is a link to download a sample solution file:

DAX to calculate dynamically within Selected Group 2022-08-10.pbix

Showcase Report – Contoso By SpartaBI

Re: DAX to calculate dynamically within Selected Group

vishwa_sg — Wed, 10 Aug 2022 15:15:21 GMT

Hi SpartaBI Thank you so much.

By using your formula. I managed to achieve the expected result. Have to calculate within a group and here is the working DAX

Oppty Engaged = SUMX(SUMMARIZE(Contact,Contact[Id] , "res", divide(distinctcount('Fact Oppty Engaged'[OptyID]), calculate(distinctcount('Fact Campaign Member'[CampaignType]), allselected('Fact Campaign Member'[CampaignType])))),[res])

Re: DAX to calculate dynamically within Selected Group

SpartaBI — Wed, 10 Aug 2022 15:21:16 GMT

my pleasure 🙂
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543