Calculate Average from all unselected items & compare one selected item vs avg of all unselected

[Anonymous]

Hi all,

I would like to have one simple line chart in power bi, where I compare one selected item (lets say product) versus average of all other products.

I have table (green one). I want to select in slicer one product - for example product Beta. In chart I will see sales values for this selected product and also I want to see a another line (average of other products = unselected).

I am enclosing two printscreens where you can see my idea in excel file.

Thank you very much for any advice!

Regards

Ondrej Hurt

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Try steps  as below:

1.Create a measure in  table

Rest_of_Prod_Avg =
CALCULATE (
    SUM ( 'Table'[sales] ),
    EXCEPT ( ALL ( 'Table'[Product] ), FILTERS ( 'Table'[Product] ) )
)
    / COUNTROWS ( EXCEPT ( ALL ( 'Table'[Product] ), FILTERS ( 'Table'[Product] ) ) )

2.Create another  calculate table:

Legand = UNION(DISTINCT('Table'[Product]),{"Rest"})

Add a measure here:

Measure =
IF (
    NOT "Rest" IN FILTERS ( 'Legand'[Product] ),
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER ( 'Table', 'Table'[Product] IN FILTERS ( 'Legand'[Product] ) )
    ),
    [Rest_of_Prod_Avg]
)

3.Adding measure to visualizations

It  will show as below:

Here is a demo:

PBIX URL 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoRo50]

This solution worked great for one filter.  Is there a way to add a second filter to the dax?  For instance, lets say we wanted to add "region" to the example above?  I work in education so I'm constantly working with comparisons of students with multiple variables, but I think the principles would be the same, so for consistency, I'd like to see if there is a solution for an additional column showing US or Europe rather than creating a new dataset.

For these purposes, I guess I'd like to filter and show the calculation for alpha in the US compared to (all products in Europe + all non-alpha in the US).  

thank you for any help you can give.

Josh

[Anonymous]

@v-easonf-msft 

Thank you very much, that is exactly what I wanted!! Great, thank you!