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

[Jameli]

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?

[Jihwan_Kim · Accepted solution]

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 )

[Jameli]

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.

[johnt75]

Try

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