1

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:

  1. I need the table to return only rows where there are variances
  2. 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
  • 38,724
  • 7
  • 42
  • 64
DataNinja
  • 67
  • 1
  • 9

1 Answers1

2

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Thanks @Alexis. This worked for me. One question, if I wanted to have a higher level filter like client that I wanted to be able to apply to the page, how would I do that since the tables have to be unrelated. So essentially I would want to have all bids for the given client in the slicers at one time. There are something like 20 or so bids per client, and around 4,000+ clients, so in having the slicers populated with all the bids at a time is quite a bit to scroll through. – DataNinja Dec 14 '20 at 14:59
  • 1
    You can keep existing related tables for filtering if you want. – Alexis Olson Dec 14 '20 at 15:03
  • How would that work though? So I have one underlying table that I have now copied to make two unrelated tables. If i use the client from one table in the filter, it won't actually filter the values considering there is no way for Power BI to know they are related. – DataNinja Dec 14 '20 at 15:27
  • 1
    The new slicer tables I suggested don't have `client`; they're a single column, `Bid Name`. You'd keep in place however you would have sliced on `client` before you created the two new tables. – Alexis Olson Dec 14 '20 at 15:30
  • Sorry, maybe I'm just not connecting. Are you saying there would be seperate slicer for client on the original dataset, which has all clients and bids? Then when selecting Client in that slicer, the two bid slicers would update to include only bids associated with that selected client? – DataNinja Dec 14 '20 at 15:40
  • 1
    Lack of relationships means the bid slicers would not automatically be cross-filtered in response to `client` selection. This [can be remedied](https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/) though. – Alexis Olson Dec 14 '20 at 16:09