Grouping and Comparing - maybe?

[irnm8dn]

I have a dataset inclusive of customer spend history.  I am looking for a pragmatic way to compare one selected customer, with all others.  The others being "grouped".

For instance - Imagine a trendline...show me the average monthly spend of "Customer A" as comapred to all other Customers.  The output would be two lines, one representing Customer A, the other line - all other customers.

The motivation for this is to be able compare...

All feedback welecome!

[v-shex-msft]

Hi @irnm8dn ,

You can write two measures to calculate and filter on selected records.

For example:

AVG selected =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Amount] ),
        FILTER (
            ALL ( Table ),
            YEAR ( Table[Date] ) = YEAR ( currDate )
                && MONTH ( Table[Date] ) = MONTH ( currDate )
                && [Customer] IN ALLSELECTED ( Customer[Customer] )
        )
    )

AVG Inverse =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Amount] ),
        FILTER (
            ALL ( Table ),
            YEAR ( Table[Date] ) = YEAR ( currDate )
                && MONTH ( Table[Date] ) = MONTH ( currDate )
                && NOT([Customer] IN ALLSELECTED ( Customer[Customer] ))
        )
    )

Regards,
Xiaoxin Sheng

[irnm8dn]

@v-shex-msft 

I see what you've done here, and less specific to the actual calculation, can you help me undertstand how to simply group the Selected vs. Not Selected in a simple DAX statement so that I can compare the two "groups"?

Much appreciated.

[v-shex-msft · Accepted solution]

Hi @irnm8dn ,

I'd like to suggest you take a look at following links to know more about 'allselected' function and 'in' operator.

The definitive guide to ALLSELECTED

The IN operator in DAX

Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.

Regards,

Xiaoxin Sheng