Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
safeequr
New Member

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

I have a table as below

safeequr_0-1710141017402.png

 

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

 

safeequr_1-1710141039070.png

 

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?

2 ACCEPTED SOLUTIONS

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 !! 

View solution in original post

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

View solution in original post

5 REPLIES 5
Dangar332
Super User
Super User

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 

Dangar332_0-1710143168691.png

Dangar332_1-1710143180966.png

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

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

 

 

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 !! 

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 )

 

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.