How to group values Selected Slicer value and all Unselected values separately in Power BI

[safeequr]

I have a table as below

I want the expected results as below, and value of "Selected Group" should change based on a slicer

if I use below DAX queries, its works fine.

// NettSaleCY_Selected = CALCULATE([Nett Sale CY], Table1[Group] = "A" )

// NettSaleCY_Others = CALCULATE([Nett Sale CY], Table1[Group] = "Others")

// NettSale TotalExceptSelected = CALCULATE ( [Nett Sale CY], Table1[Group] <> "A", Table1[Group] <> "Others")

but If I replace the value "A" to a value from a slicer, its throwing an error

// NettSaleCY_Selected = CALCULATE( [Nett Sale CY], Table1[Group] = SELECTEDVALUE(Table1[Group]) )

Appreciate your advise?

[Dangar332 · Accepted solution]

hi, @safeequr 

try to replace  'table'[net sales] with your measure in all measure formula

like

SALES = 
var a = SELECTEDVALUE('Table'[group])
return
CALCULATE([Measure],'Table'[group]=a)+0

other sales = 
SUMX(
    FILTER(
        ALL('Table'[group],'Table'[net sales],'Table'[product]),
        'Table'[group]="other" &&
         'Table'[product]=SELECTEDVALUE('Table'[product])
    ),
    [Measure]
)+0

NettSale TotalExceptSelected = 
var curr_group = SELECTEDVALUE('Table'[group])
return
SUMX(
    FILTER(
        ALL('Table'[group],'Table'[net sales],'Table'[product]),
        AND('Table'[group]<>"other",'Table'[group]<>curr_group) &&
         'Table'[product]=SELECTEDVALUE('Table'[product])
    ),
    [Measure]
)+0

if still not resolve your problem then provide some data with your measure

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! 

[Dangar332]

hi, @safeequr 

try below measures 

for sales of selected value

SALES = 
var a = SELECTEDVALUE('Table'[group])
return
CALCULATE(SUM('Table'[net sales]),'Table'[group]=a)+0

for others

other sales = 
SUMX(
    FILTER(
        ALL('Table'[group],'Table'[net sales],'Table'[product]),
        'Table'[group]="other" &&
         'Table'[product]=SELECTEDVALUE('Table'[product])
    ),
    'Table'[net sales]
)+0

for NettSale TotalExceptSelected

NettSale TotalExceptSelected = 
var curr_group = SELECTEDVALUE('Table'[group])
return
SUMX(
    FILTER(
        ALL('Table'[group],'Table'[net sales],'Table'[product]),
        AND('Table'[group]<>"other",'Table'[group]<>curr_group) &&
         'Table'[product]=SELECTEDVALUE('Table'[product])
    ),
    'Table'[net sales]
)+0

see below image 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! 

[safeequr]

Hi @Dangar332 

Thanks for the solution.

First DAX qurey works fine. but the 2nd & 3rd is not working as  in my table the field, 'Table'[net sales]

 is calulated measure not a column. I tried applying the qurey of 'Table'[net sale] directly here. but still its throwing error

[Dangar332 · Accepted solution]

hi, @safeequr 

try to replace  'table'[net sales] with your measure in all measure formula

like

SALES = 
var a = SELECTEDVALUE('Table'[group])
return
CALCULATE([Measure],'Table'[group]=a)+0

other sales = 
SUMX(
    FILTER(
        ALL('Table'[group],'Table'[net sales],'Table'[product]),
        'Table'[group]="other" &&
         'Table'[product]=SELECTEDVALUE('Table'[product])
    ),
    [Measure]
)+0

NettSale TotalExceptSelected = 
var curr_group = SELECTEDVALUE('Table'[group])
return
SUMX(
    FILTER(
        ALL('Table'[group],'Table'[net sales],'Table'[product]),
        AND('Table'[group]<>"other",'Table'[group]<>curr_group) &&
         'Table'[product]=SELECTEDVALUE('Table'[product])
    ),
    [Measure]
)+0

if still not resolve your problem then provide some data with your measure

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! 

[safeequr]

Hi @Dangar332 ,

Thanks for the reply.

I tried the above, but error comes in the highlighted part, as the ALL function accepts only column names not calculated measures or columns. I replaced Table'[net sales] with  my measure. but still not accepting.

other sales =
SUMX(
FILTER(
ALL('Table'[group],'Table'[net sales],'Table'[product]),

////   Table[net sales] is calculated as below.. //

Table[net sales] = CALCULATE (SUM ( 'Airfact'[Sale] ),'Scenario Multiple Comparisons Issue Dates'[Scenario]="TY" ,'Flown Comparison'[Flown Scenario]="TY" ,'AirFact'[IsSale]=1 )

[Dangar332 · Accepted solution]

hi, @safeequr 

sorry for providing miscalculated code 

i forgot to remove Table'[net sales] from code as it measure so try below

SALES = 
var a = SELECTEDVALUE('Table'[group])
return
CALCULATE([Measure],'Table'[group]=a)+0

other sales = 
SUMX(
    FILTER(
        ALL('Table'[group],'Table'[product]),
        'Table'[group]="other" &&
         'Table'[product]=SELECTEDVALUE('Table'[product])
    ),
    [net sales]
)+0

NettSale TotalExceptSelected = 
var curr_group = SELECTEDVALUE('Table'[group])
return
SUMX(
    FILTER(
        ALL('Table'[group],'Table'[product]),
        AND('Table'[group]<>"other",'Table'[group]<>curr_group) &&
         'Table'[product]=SELECTEDVALUE('Table'[product])
    ),
    [net sales]
)+0

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly