Variance between row values in same table based on selected filter values - Power BI

Question

I am trying to return the variance values between rows based off of multiple filter values.

Data set looks like this:

Bid Name	Service Area	Task Name	Resource	Hours	Revenue
Bid 1	SA1	Fix the lights	R1	75	100
Bid 2	SA1	Fix the lights	R1	100	175

What I am looking for, is something like this:

Slicer 1
Bid 1 (selected)
Bid 2

Slicer 2
Bid 1
Bid 2 (selected)

Service Area	Task Name	Resource	Hours	Revenue
SA1	Fix the lights	R1	25	75

I have tried a lot of variations using calculate and all selected and tried copying the table and using one slicer on table 1 and the other on table 2. A few nuances:

I need the table to return only rows where there are variances
If the same bid is selected in both slicers, the values should return nothing (as there should be no variances

If there is a better way to do this without the slicers I am fine to do it without.

Any help is greatly appreciated.

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-11T23:24:23

You'll need to create a separate table for each slicer and neither of these slicer tables should be related to your data table or each other.

That is, two calculated tables

Slicer1 = VALUES ( Data[Bid Name] )
Slicer2 = VALUES ( Data[Bid Name] )

Then you write a measure that reads the slicers and calculates the difference (and returns a blank if the difference is zero).

HourDiff =
VAR Bid1 = SELECTEDVALUE ( Slicer1[Bid Name] )
VAR Bid2 = SELECTEDVALUE ( Slicer2[Bid Name] )
VAR Hours1 = CALCULATE ( SUM ( Data[Hours] ), Data[Bid Name] = Bid1 )
VAR Hours2 = CALCULATE ( SUM ( Data[Hours] ), Data[Bid Name] = Bid2 )
RETURN
    IF (
        ISBLANK ( Hours1 ) || ISBLANK ( Hours2 ) || Hours1 = Hours2,
        BLANK(),
        Hours2 - Hours1
    )

The measure for RevenueDiff is analogous.

Variance between row values in same table based on selected filter values - Power BI

1 Answers