Forum Discussion

Sdg8481's avatar
Sdg8481
Icon for Helper I rankHelper I
8 years ago
Solved

If a % then Average else sum

Hi,   Apologies i'm fairly new to Power BI and i just can't figure out how to do something.   Basically i have a table of data with a value field. This value column contains a mixture of Numbers ...
  • Sdg8481's avatar
    Sdg8481
    8 years ago

    Thank you this is brilliant. I was so close!

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Sdg8481,

you can create a measure in this case instead:

new = IF(MAX('Main'[Combined Medatada.Value type]) = "Percentage", AVERAGE('Main'[Value]), SUM('Main'[Value]))

  • Sdg8481's avatar
    Sdg8481
    Icon for Helper I rankHelper I
    8 years ago

    Thank you this is brilliant. I was so close!

  • Sdg8481's avatar
    Sdg8481
    Icon for Helper I rankHelper I
    8 years ago

    I'm so sorry, but i have a followup question if you could be so kind as to advise.

     

    The value column is also used in a LOOKUPVALUE column to pull through the equivalent value for the same date in the previous Year (Prior Yr Value = LOOKUPVALUE(Main[Value],Main[Measure_Date Key],Main[Measure_EquivDate Key])).

     

    How can i combine this Lookupvalue and the measure created above, so that it produces an average for the prior yr value for those marked as "Percentage" and sums the rest. As it seems i can't use the "new" measure in the LOOKUP calculation

     

    Ie: combining the following:

    Prior Yr Value = LOOKUPVALUE(Main[Value],Main[Measure_Date Key],Main[Measure_EquivDate Key])

    new = IF(MAX('Main'[Combined Metadata.Value type]) = "Percentage", AVERAGE('Main'[Value]), SUM('Main'[Value]))

     

    Many Thanks

    S