Control serveral Measures with Fieldparameters

[showy]

Hello
I have the following problem. In my PBI report I have 3 different revenues, the constant quantity and I want to calculate the average price per month. The whole thing has to be controlled with a field parameter. Now this works very well in the table. I just have problems getting the revenue selection into the price measure.

I have to somehow tell the price measure which selection was made in the field parameter.
I would like to control the turnover via the field parameter and the price should be calculated automatically according to the selection.

Does anyone have any ideas?

Table:
Date Revenue1 Revenue2 Revenue3 Quantity
Jan 100 125 158 1100
Feb 200 202 187 1203
Mar 150 148 160 1123
Apr 133 102 111 1236
May 250 205 188 1562
Jun 212 199 223 1452
Jul 165 170 190 1326
Aug 136 139 145 2003
Sep 102 125 133 2010
Oct 126 133 155 1856
Nov 210 202 188 1887
Dec 180 178 161 1957

Measures used:

Revenue I = SUM('Table'[Revenue1])
Revenue II = SUM('Table'[Revenue2])
Revenue III = SUM('Table'[Revenue3])
Quantity = SUM('Table'[Quantity])
AVG Price = DIVIDE( [Revenue I], [Quantity], BLANK() )

Field parameter:

Selected Revenue = {
("Revenue I", NAMEOF('Table'[Revenue I]), 0),
("Revenue II", NAMEOF('Table'[Revenue II]), 1),
("Revenue III", NAMEOF('Table'[Revenue III]), 2)
}

I know that if I have the Revenue I in the price measure, I will not get the correct value for the price when I switch to Rev2 or Rev3. But I don't know what I have to replace the revenue part with in the price measure to get the selection from the field parameter.

[Anonymous · Accepted solution]

Hi @showy ,

Please try:

Create field parameters:

Create a measure in 'Table' table:

AVG Price = SWITCH(
    TRUE(),
    SELECTEDVALUE(Parameter[Parameter Fields]) = "'Table'[Revenue1]",DIVIDE(MAX('Table'[Revenue1]), MAX('Table'[Quantity])),
    SELECTEDVALUE(Parameter[Parameter Fields]) = "'Table'[Revenue2]",DIVIDE(MAX('Table'[Revenue2]), MAX('Table'[Quantity])),
    SELECTEDVALUE(Parameter[Parameter Fields]) = "'Table'[Revenue3]",DIVIDE(MAX('Table'[Revenue3]), MAX('Table'[Quantity]),
    BLANK()
))

'Table'[Revenue1] is placed in Switch because SELECTEDVALUE(Parameter[Parameter Fields]) returns this:

Drag the fields as shown below into the table visual:

The final page effect is as shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[showy123]

Hi Yang,
Thanks for your reply.

The behavior you built in the demo file is exactly what I want.
But there is still a difference for my case. I have a lot of values per month in my real file and therefore have to use SUM(Rev1), SUM(Rev2) and SUM(Rev3).
I then get the error message

The column "Revenue1" in the table "Table1" was not found or may not be used in this expression.

[Anonymous]

Hi @showy  @showy123 ,

Thank you for your prompt reply.

You mentioned a lot of values per month, OK, So:

Create a month column:

Month = 'Table'[Date].[Month]

Create a sort column by which the month column is sorted:

Year Month Sort = YEAR('Table'[Date])*12+MONTH('Table'[Date])

Create 4 columns respectively to calculate the sum of Revenue and Quantity:

Revenue I = CALCULATE(SUM('Table'[Revenue1]),ALLEXCEPT('Table','Table'[Month]))

Revenue II = CALCULATE(SUM('Table'[Revenue2]),ALLEXCEPT('Table','Table'[Month]))

Revenue III = CALCULATE(SUM('Table'[Revenue3]),ALLEXCEPT('Table','Table'[Month]))

SumQuantity = CALCULATE(SUM('Table'[Quantity]),ALLEXCEPT('Table','Table'[Month]))

Build parameters:

Create a measure:

AVG Price = SWITCH(
    TRUE(),
    SELECTEDVALUE(Parameter[Parameter Order]) = 0,DIVIDE(MAX('Table'[Revenue I]), MAX('Table'[SumQuantity])),
    SELECTEDVALUE(Parameter[Parameter Order]) = 1,DIVIDE(MAX('Table'[Revenue II]), MAX('Table'[SumQuantity])),
    SELECTEDVALUE(Parameter[Parameter Order]) = 2,DIVIDE(MAX('Table'[Revenue III]), MAX('Table'[SumQuantity]),
    BLANK()
))

The final page result is shown below:

The pbix file is attached.

If you have other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[showy123]

Hi

from your first approach i found a solution:

AVG Price =

VAR _Selection = SELECTEDVALUE('Parameter[Parameter Fields])

RETURN

SWITCH(

    TRUE(),

    _Selection = "'Parameter'[Revenue1]", DIVIDE([Revenue1], [Quanity]),

    _Selection = "'Parameter'[Revenue3]", DIVIDE([Revenue2], [Quanity]),

    _Selection = "'Parameter'[Revenue3]", DIVIDE([Revenue3], [Quanity]),

    BLANK()

)



This one works perfect in my case 

[Anonymous]

Hi @showy123 ,

My heartfelt congratulations on getting your issue resolved.

This will be of great help to users who encounter similar problems in the future.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!