topic Re: Dax help needed with outlier case for all selected groups (ALLSELECTED) in DAX Commands and Tips

Dax help needed with outlier case for all selected groups (ALLSELECTED)

Jameli — Tue, 23 Aug 2022 08:29:12 GMT

I have a case where I would have to calculate a % of total of sales for 2 selected groups but only one of them is in visual.

Here's my data in picture

So I would like to first select Groups B and C, then select products only from C (Green and Yellow grapes). Then I would like to see the percentage of sales Green and Yellow Grapes contributed to Total sales of Groups B and C.

So Desired outcome would be for Green grapes (30 / 70) ~43% and for Yellow Grapes (10/70) ~14%.

I tried using ALLSELECTED but it does not work when I only have Group C products selected in visual.

This video got me pretty close, but it's not working since there are only Group C products in visual:

DAX Fridays! #118: Get selected or multiple values from slicers using DAX

Any ideas is this even possible or how to proceed?

Re: Dax help needed with outlier case for all selected groups (ALLSELECTED)

johnt75 — Tue, 23 Aug 2022 08:45:59 GMT

Try

% sales of selected group = VAR currentSales = [Sales] VAR selectedGroupSales = CALCULATE ( [Sales], ALLEXCEPT ( 'Product', 'Product'[Group] ) ) RETURN DIVIDE ( currentSales, selectedGroupSales )

Re: Dax help needed with outlier case for all selected groups (ALLSELECTED)

Jihwan_Kim — Tue, 23 Aug 2022 08:52:34 GMT

Hi,

I am not sure how your dataset looks like, but one of ways to create a solution is to have dimension table for Group, like below. In this case, Group slicer won't be influenced by the product selection.

Percent of sales from selected groups: = VAR _sales = [Sales measure:] VAR _selectedgroup = VALUES ( 'Group'[Group] ) VAR _grouptotal = CALCULATE ( [Sales measure:], FILTER ( ALL ( Data ), Data[Group] IN _selectedgroup ) ) RETURN DIVIDE ( _sales, _grouptotal )

Re: Dax help needed with outlier case for all selected groups (ALLSELECTED)

Jameli — Tue, 23 Aug 2022 10:01:27 GMT

Thanks, this is how I would like it to work.

This unfortunately doesn't work straight away in my case since the dimension table is not joined by Group -field but another field. I could solve this by creating a another separate dimension which is joined by the Group field, but I'll have to think if I want to create a separate solution for this.

Maybe creating an inactive relation by group and using it in your solution with USERRELATIONSHIP would work as well.