Show different Values (Average / SUM) in single bar graph based on selected slicer option

[Aishu_2609]

Dear Team,

I want to show the average of some values and sum of some values based upon the selected slicer option. Say for example, if we have a table A which has columns named products, months, country  and sales value. Products column had books and phones. If user selects books the value should be shown as average of Sales Value  column  keeping months, country constant (same as FIXED function in tableau) and if user selects phones sum of sales value should be shown keeping months, country constant.

Your support is appreciated.

thanks

[Aishu_2609]

@v-easonf-msft Manny thanks for this , can you kindly confirm if I can use Msales as a slicer and slicer will show books and phones as 2 options to select?

[v-easonf-msft · Accepted solution]

Hi, @Aishu_2609

Books and phones are the contents of the "Products" field where I group them in the formula.

If the results are incorrect, please share sample data and expected results for further research.

Best Regards,
Community Support Team _ Eason

[Aishu_2609]

Many Thanks 

[v-easonf-msft]

Hi,  @Aishu_2609 

Please try measure like blow:

M_Sales =
SWITCH (
    SELECTEDVALUE ( 'Table'[Products] ),
    "Books",
        CALCULATE (
            AVERAGE ( 'Table'[sales] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[country] = MAX ( 'Table'[country] )
                    && 'Table'[Products] = MAX ( 'Table'[Products] )
            )
        ),
    "Phones",
        CALCULATE (
            SUM ( 'Table'[sales] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[country] = MAX ( 'Table'[country] )
                    && 'Table'[Products] = MAX ( 'Table'[Products] )
            )
        )
)

Best Regards,
Community Support Team _ Eason