If a % then Average else sum

[Sdg8481]

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

[Anonymous]

@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]

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

[Sdg8481 · Accepted solution]

Thank you this is brilliant. I was so close!