Multiple What IF Parameter for each row of a matrix visual

[BI_Pro]

Hello! hope all are doing well.
I'm trying to build a matrix visual with multiple what if parameters. The requirement is that each what parameter will be associated with one row. Please refer to the data and matrix view, below. Now the metrix will have Entity as row and Permanent count, Contractor (cont) count and Cont % as Col. Due to some regulation, contractor comes with additional cost (say $100/cont)and if the % of Cont for that entity crosses 50% then further cost (say $400/cont on top of $100) applicable. User wants to know for an entity, if additional contractor is hired then what will be the additional cost. That means one what if parameter for each entity.
I've managed to do it for one entity. But how do I add one more parameter which is associated with Entity 2 and will show the additional cost within same col? Is it practically possible?

Matrix View

Entity	Employee	Employee Type
Entity1	1001	Perm
Entity1	1002	Perm
Entity1	1003	Perm
Entity1	1004	Perm
Entity1	1005	Perm
Entity1	1006	Perm
Entity1	1007	Cont
Entity1	1008	Cont
Entity1	1009	Cont
Entity1	1010	Cont
Entity2	1011	Perm
Entity2	1012	Perm
Entity2	1013	Perm
Entity2	1014	Perm
Entity2	1015	Cont
Entity2	1016	Cont
Entity2	1017	Cont
Entity2	1018	Cont

[v-yangliu-msft · Accepted solution]

Hi  @BI_Pro ,

Here are the steps you can follow：

1. Use New Parameter to create two parameters

2. Create measure.

extra cont cost =
var _para1=SELECTEDVALUE(Parameter[Parameter])
var _para2=SELECTEDVALUE('Parameter2'[Parameter2])
return
IF(MAX('Table'[Entity])="Entity1",
    IF(
    [Cont%]<0.5,
   ( _para1+[Cont Total])*100,
   400/( _para1+[Cont Total])+100),
   IF(
    [Cont%]<0.5,
   ( _para2+[Cont Total])*100,
   400/( _para2+[Cont Total])+100))

3. Result:

When Parameter is set to 3, it is displayed as 700 in Entity1, and there is no change in Entity2.

When Parameter2 is set to 4, there is no change in Entity1, and Entity2 is displayed as 150.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yangliu-msft · Accepted solution]

Hi  @BI_Pro ,

Here are the steps you can follow：

1. Use New Parameter to create two parameters

2. Create measure.

extra cont cost =
var _para1=SELECTEDVALUE(Parameter[Parameter])
var _para2=SELECTEDVALUE('Parameter2'[Parameter2])
return
IF(MAX('Table'[Entity])="Entity1",
    IF(
    [Cont%]<0.5,
   ( _para1+[Cont Total])*100,
   400/( _para1+[Cont Total])+100),
   IF(
    [Cont%]<0.5,
   ( _para2+[Cont Total])*100,
   400/( _para2+[Cont Total])+100))

3. Result:

When Parameter is set to 3, it is displayed as 700 in Entity1, and there is no change in Entity2.

When Parameter2 is set to 4, there is no change in Entity1, and Entity2 is displayed as 150.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[BI_Pro · Accepted solution]

Thanks v-yangliu-msft. 

I was thinking about something similar but unable to nail it down

[TomMartens]

Hey @BI_Pro ,

I recommend that you have a closer look to data modeling with Power BI: Model data in Power BI - Learn | Microsoft Docs

Without understanding the requirements completely, I would recommend creating an Entity table that stores additional information about the specific entity. Relate this table to your existing table.

Create a measure that iterates across the entities, inside each iteration you can access the specific entity information.

You might want to avoid data modeling (even if it's considered best practice to start with a sound data model) you can create a DAX that looks like this.

meassure 1 =

SUMX(
    'table'
    , var __Entity = 'table'[Entity]
    return
    SWITCH( __Entity
        "Entity1" , CALCULATE( ... )
        , "Entity2" , CALCULATE( ... )
    )
)

Hopefully, this provides something that will help to tackle your challenge.

Regards,

Tom

[BI_Pro]

Thanks. Individual what if parameter is working (for each row) but I'm not getting the desire result for the calculation. It's getting multiplied by the number of rows per entity i.e. if the desire result of the calculation for Entity1 is 100 then i'm getting 1000. It's because there are 10 employees under Entity1.