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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
TCGH
Regular Visitor

Variance /slicer dependent/ need help for the formula

Hello

I am trying to visualize the variance between production plans. I tried a simple formula like this one:

SelectedCriteria = SELECTEDVALUE('SlicerTable'[Criteria])
Measure1 = CALCULATE(SUM('PRODUCTION'[Volumes]), 'PRODUCTION'[Plan] = SelectedCriteria)
Measure2 = CALCULATE(SUM('PRODUCTION'[Volumes]), 'PRODUCTION'[Plan] = OtherCriteria)
Variance = [Measure1] - [Measure2]
 
Or a DAX formula. it is always giving me a synthax error. So either I am not adapting the formula correctly or the formulas are not correct.

Let us try from the start. 
Within the same table of production volume, I have different production plans, let us say its plan 1 to 10.
In my visual, I have a table with the products and the volume. On top a slicer that shows the plans 1 to 10.
The slicer is multiple selection. 
What I can do now is to click on the slicer on plan 3 and 4 as an example, and below i can see the products and their planned volumes in 2 different colums, one under each plan. I need to create a variance between thse 2 plans to see what is difference in terms of production volume for each product. If I click on a thrid or 4th plan, they will show up. So then I am not sure what will the variance show up, it is great if it just show up in the last column and calculates the variance between the last 2 colums only. That way there is no mistakes in knowing what variance is calculated. 

any help is appreciated. 


 
1 ACCEPTED SOLUTION

Hi @TCGH ,

 

If you select multiple plans in one slicer, you can't locate the one you selected.

As you can see, below both of these scribing formulas are the same, the computer doesn't recognize the first selected plan you are talking about, and the second plan.

vstephenmsft_0-1722232441000.png

You have to create two tables like I did to implement your first slicer's choice and second slicer's choice, and the two slicers were not related to each other.

 

Best Regards,

Stephen Tao

 

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
v-stephen-msft
Community Support
Community Support

Hi @TCGH ,

 

If you want to compare the values of two slicers, it's a good idea to have fields from two different tables, and the tables of the two slicers are not related to your main table.

Here's an example for reference:

vstephenmsft_1-1721978519670.png

Measure = var _sel1=SELECTEDVALUE(Slicer1[Criteria])
var _sel2=SELECTEDVALUE('Slicer2'[Criteria])
return CALCULATE(SUM('Table'[Volumes]),FILTER('Table',[Criteria]=_sel1))-CALCULATE(SUM('Table'[Volumes]),FILTER('Table',[Criteria]=_sel2))

vstephenmsft_0-1721978507356.png

Best Regards,

Stephen Tao

 

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

 

Hello Stephen
We are getting somewhere. 
I used your formula (without creating slicers in the table). By using one slicer from the Visualizations with multiple choice.
I updated the formula with the names of the table and data:

MeasureVARSPP = var _sel1=SELECTEDVALUE(PRODUCTION[Plan])
var _sel2=SELECTEDVALUE(PRODUCTION[Plan])
return CALCULATE(SUM('PRODUCTION'[Volumes]),FILTER('PRODUCTION',[Plan]=_sel1))+CALCULATE(SUM('PRODUCTION'[Volumes]),FILTER('PRODUCTION',[Plan]=_sel2))
 
Whereas when I select 2 Plans in the slicer, I get the following:
Plan 1 Plan 2 Plan Total  
Prod VolumMeasureVARSPPProd VolumMeasureVARSPPProd VolumMeasureVARSPP
100000120000220000 
200000160000360000 

 

I changed the minus into + in the formula and got this:

Plan 1 Plan 2 Plan Total  
Prod VolumMeasureVARSPPProd VolumMeasureVARSPPProd VolumMeasureVARSPP
10000200001200024000220000 
20000400001600032000360000 

 

Strangely, it is not showing me the variance, in both cases. 
It is only adding up and in the second case it shows a measure that is double (probably due to +) and the total stays a double. 

Hi @TCGH ,

 

If you select multiple plans in one slicer, you can't locate the one you selected.

As you can see, below both of these scribing formulas are the same, the computer doesn't recognize the first selected plan you are talking about, and the second plan.

vstephenmsft_0-1722232441000.png

You have to create two tables like I did to implement your first slicer's choice and second slicer's choice, and the two slicers were not related to each other.

 

Best Regards,

Stephen Tao

 

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

 

TCGH
Regular Visitor

Hi
I view your youtube and tried your solution and came up with this formula:

MeasureVol = CALCULATE(SUM('PRODUCTION'[Volumes]),Filter(all('PRODUCTION'[Plan]),'PRODUCTION'[Plan] in ALLSELECTED('PRODUCTION'[Plan])))
 
What I end up with is having one additional measured column which is equal to the prod plan I selected in the slicer. When I select the second plan, I get the same: an additional column with the same volume nb. 

What I need is the variance between these columns. I want only one column showing Plan 1 - Plan 2.
How can I customzie the formula something like that

MeasureVol = CALCULATE(SUM('PRODUCTION'[Volumes]),Filter(all('PRODUCTION'[Plan]),'PRODUCTION'[Plan] in ALLSELECTED('PRODUCTION'[Plan]))) - CALCULATE(SUM('PRODUCTION'[Volumes]),Filter(all('PRODUCTION'[Plan]),'PRODUCTION'[Plan] in ALLSELECTED('PRODUCTION'[Plan])))
 
Whereas the first part should be for Plan 1 and second part for plan 2 in a way that it recognized that the first selection from the slicer is plan 1 and second is part 2
amitchandak
Super User
Super User

@TCGH , if want compare two categorical values using slicer, consider

 

Compare Categorical Data Using Slicers, Compare Two Brands/Categories/Cities: https://youtu.be/exN4nTewgbc

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.