Forum Discussion

showy's avatar
showy
Helper II
2 years ago
Solved

Control serveral Measures with Fieldparameters

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...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
showy123
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    showy123
    Frequent Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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!