Creating a variance measure for values derived by on-page filters

[DJBAJG]

First off, sorry for the title. I'm struggling to figure out how to ask the questions appropiately. With that said, here's my question.

Is there a way to display the vaiance between two on-page values that have been created by filtering a single value two different ways?

ie. I have a dollar amount in a single table, let's call it total cost. On the page I display that number in two seperate charts. The chart on the left is filtered by a set of on page slicers which give me a certain value. The chart on the right is filtered by another set of slicers that give me a differnt value. I'm using the edit interactions feature to make these two dissimilar values possible whereby the slicers on one side of tha page do not affect he other side and vise-versa.

My initial thought is that since this value comes from one source (a single table) there's no way for Power BI to reference the different filtered values on the page in a measure to calcualate the difference. Please tell me I'm wrong.

I've attached a screenshot of the scenario I'm talking about. 

[v-shex-msft · Accepted solution]

HI @DJBAJG,

>>My initial thought is that since this value comes from one source (a single table) there's no way for Power BI to reference the different filtered values on the page in a measure to calcualate the difference. Please tell me I'm wrong.

Yes, the fact is similar to your thinking. Current power bi not able to use Dax expression to extract different values from the same data source fields.  

For your requirements, you need to extract these field values to create a new table and use the new table fields as the source of the filter. Then you can compare values between two different sources with filter effects.

Regards,

Xiaoxin Sheng

[DJBAJG · Accepted solution]

Median Copay Category 3 =

VAR Location = ALLSELECTED('Table 3'[Location])

VAR PlanType = ALLSELECTED('Table 3'[Plan Type])

VAR OrganizationName = ALLSELECTED('Table 3'[Organization name])

return

CALCULATE([Median Copay],

'Organization NEW'[Location] IN Location,

'Organization NEW'[Plan Type] IN PlanType,

'Organization NEW'[Organization name] IN OrganizationName)

[DJBAJG]

I appreciate the thought but unfortunately duplication of the source table or columns is not an option. We're dealing with TBs of data and the model structure won't support that increase in size. I tried to employ another type of solution using unrelated category tables and a variable selection. It works for a single category, 'Location" in the code below, but I need it to function for multiple slicers/categories. Is there a way to modify the code to accept multiple filters?

The below code works for a single category/slicer:

Median Copay Category 1 =

VAR selectedCategory = ALLSELECTED('Table 1'[Location])

return

CALCULATE([Median Copay],

FILTER('Organization NEW','Organization NEW'[Location] IN selectedCategory))

Can I make it take multiple filters? The code below gives me an error stating "Function CONTAINSROW must have a value for each column in the table expression".

Median Copay Category 3 =

VAR selectedCategory = ALLSELECTED('Table 3'[Location],'Table 3'[Plan Type])

return

CALCULATE([Median Copay],

'Organization NEW'[Location] IN selectedCategory,

'Organization NEW'[Plan Type] IN selectedCategory)

[DJBAJG]

Still need help here....

[v-shex-msft]

HI @DJBAJG,

You can consider creating a calculated table with all or values function to choose specific table field values.

ALL  

VALUES 

NEW Table=VALUES(Table1[Column])

or 

NEW Table=ALL(Table1[Column])

Measure formula:

Diff =
CALCULATE ( SUM ( Table1[Amount] ), ALLSELECTED ( Table1 ) )
    - CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER ( ALL ( Table1 ), [Column] IN ALLSELECTED ( 'NEW Table'[Columns] ) )
    )

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

HI @DJBAJG,

>>My initial thought is that since this value comes from one source (a single table) there's no way for Power BI to reference the different filtered values on the page in a measure to calcualate the difference. Please tell me I'm wrong.

Yes, the fact is similar to your thinking. Current power bi not able to use Dax expression to extract different values from the same data source fields.  

For your requirements, you need to extract these field values to create a new table and use the new table fields as the source of the filter. Then you can compare values between two different sources with filter effects.

Regards,

Xiaoxin Sheng

[DJBAJG]

With that being said I now need a simple visual to show the variance. What I want is a bar that has 0 in the middle and if he variance is positive the bar extends left and is green and if it's negative the bar extends right and is red. I feel this should be easily achievable with a standard clustered bar chart but when I place my variance measure on the chart I don't get and option to set conditional formatting under data colors. I'm open to using a custom visual if necesary, perhaps something with arrows or otherwise noting which scenario is larger or smaller.

[DJBAJG]

Ok, so I guess I jumped the gun. I'm not sure how to execute what you mentioned about extracting the values into a new table and using them as the filter. My dataset is too large to duplicate raw source elements. Were you saying to do that or create something else in a new table??