March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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?
Solved! Go to 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 !!
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
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 !!
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |