Sum and average values in one measure

[Kylie]

Hi,

I have a measure always filtered by one text column (Type Name):

These values are historized on a quarterly basis (with the first date of the quarter). And now I need to implement the following logic: when evaluating multiple quarters at the same time (for example, having multiple choice in the date column filter), the measure numbers should be averaged. In the case of one quarter, the values should be summarized.

I can't think of any ideas, so any tips will help me!

Thanks in advance

Kylie

[Anonymous · Accepted solution]

Hi @Kylie,

You can write a measure with a switch function to check the filter selections, then you can navigate to the different expressions based on the selected value.

formula =
VAR selceted =
    SELECTEDVALUE ( NewTable[Type Name] )
RETURN
    SWITCH (
        selceted,
        "Sum", SUM ( Table[Sale] ),
        "Average", AVERAGE ( Table[Sale] )
    )

Regards,

Xiaoxin Sheng

[Whitewater100]

Hi Kylie:

Please see attached file with the results I hope you are excpecting.

https://drive.google.com/file/d/1JFg76rkWlb4PgO361geAg5Jz_ImVTawk/view?usp=sharing 

I have set up a file with a basic model and calulations. If you select one quarter,the results are just for that Qtr, when selecting multiple Q's the results are averaged. I used some made up data to demonstrate.