CODEWITHSUNDEEP
filterpowerbidaxmeasure
Aloys
Aloys

Reputation: 42

Measure with two separately filtered values in the same dimension, sliced by one of the filters in the visual

I have a dimension table for "Process" and two fact tables for "FTE" and "Volume". Both are related to "Process". There is a fourth table that I want to show results by, the Entities (Organizations).

id   Process
---  -------
1    Approval
2    Payment
3    Process X
4    Process Y

id   Entity
---  -------
1    Entity A
2    Entity B

id   Process   Entity      Volume
---  -------   -------     ------
1    1         Entity A    100
2    1         Entity B    300
3    3         Entity A    50
4    3         Entity B    25

Please note: there are no volumes for process 2. Process 1 volumes are relevant for process 2 as well. First, invoices (the volume) are approved, then they are paid. However, the volume is mapped to the "Approval" process only, not the "Payment" process.

The FTE table has the same structure as Volume, except with a FTE column. There are FTE for all processes.

id   Process   Entity      FTE
---  -------   -------     ----
1    1         Entity A    1
2    2         Entity A    0.5
3    1         Entity B    1.2
4    2         Entity B    0.6

I want to calculate a Volume per FTE measure. So, what we need is a measure to divide Volume by FTE, where Volume is filtered by process 1 (Approval) and FTE by process 2 (Payment).

Then I want to be able to see the outcome in a visual with a slicer set to process 2 "Payment".

Slicer:

-----------------------
Process = "Payment"
-----------------------

Visual (table):

------------------------------------------
| Entity      Measure: Volume per FTE    |
| ---------   ---------------------------|
| Entity A    100 / 0.5 = 200            |
| Entity B    300 / 0.6 = 500            |
------------------------------------------

A straightforward DIVIDE(CALCULATE(), CALCULATE()) doesn't work as the Volume will be filtered out by the slicer, returning a BLANK() result.

Is there a way to do this? E.g. can we fool PowerBI to filter the volume on process 1, thinking it is doing it with process 2? Or some other way?

Thanks!

Upvotes: 0

Views: 94

Answers (1)

mxix
mxix

Reputation: 3659

So... still a bit confusing. You might need to rethink your model.

But you could start with:

Volume for Approval = CALCULATE(SUM('Volume'[Volume]), 'Process'[Process] IN { "Approval" })

And that way I will overwrite any filter from the slicer on that column.

Volume per FTE = DIVIDE( Volume[Volume for Approval], SUM(FTE[FTE]))

Output

Upvotes: 1

Related Questions