cancel
Showing results 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

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
Community Support

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.

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.

5 REPLIES 5
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:

``````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))``````

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.

Regular Visitor

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 Volum MeasureVARSPP Prod Volum MeasureVARSPP Prod Volum MeasureVARSPP 10000 0 12000 0 22000 0 20000 0 16000 0 36000 0

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

 Plan 1 Plan 2 Plan Total Prod Volum MeasureVARSPP Prod Volum MeasureVARSPP Prod Volum MeasureVARSPP 10000 20000 12000 24000 22000 0 20000 40000 16000 32000 36000 0

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.

Community Support

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.

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.

Regular Visitor

Hi

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

Announcements

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 Monthly Update - August 2024

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors