Forum Discussion

Sdg8481's avatar
Sdg8481
Helper 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 and Percentages (although all in whole number format) for each day, there is however a field called ValueType that contains either "Percentage" or "Number".

 

I have a table in my report that aggregates this value field for the date range selected by the user, this is current set to SUM. However, for those rows ValueType="Percentage" i need to calculate the average not the sum. Is this possible, if so where and how to apply formula.

 

btw, i have tried adding the following into a new column, but this just seems to sum or weight evertrhing in the table, irrelevant of the dates and any other criteria the user has filtered the report by

 

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

 

Any help will be greatly appreciated.

Thanks

Stu

  • Sdg8481's avatar
    Sdg8481
    8 years ago

    Thank you this is brilliant. I was so close!

3 Replies

  • 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
      Helper I
      8 years ago

      Thank you this is brilliant. I was so close!

    • Sdg8481's avatar
      Sdg8481
      Helper 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