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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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