CODEWITHSUNDEEP
pivot-tabledax
BarneyW
BarneyW

Reputation: 79

Displaying differences with PowerPivot

Based on the following Data:

Location    Salesperson Category    SalesValue
North       Bill        Bikes       10
South       Bill        Bikes       90
South       Bill        Clothes     250
North       Bill        Accessories 20
South       Bill        Accessories 20
South       Bob         Bikes       200
South       Bob         Clothess    400
North       Bob         Accesories  40

I have the following Sales PivotTable in Excel 2016

             Bill   Bob
Bikes        100    200
Clothes       10    160
Accessories   40     40

I would now like to diplay the difference between Bill and Bob and, importantly, be able to sort the table by difference. I have tried adding the Sales a second time and displaying it as a difference to "Bill". This gives me the correct values but sorts according to the underlying sales value and not the computed difference. 

            Bill    Bob   Difference
Bikes        100    200          100
Clothes       10    160          150
Accessories   40     40            0

I am fairly sure I need to use some form of DAX calculation but am having difficulty finding out exactly how. Can anyone give me a pointer?

Upvotes: 0

Views: 51

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Create a measure for that calculation:

If Bill and Bob are columns in your table.

Difference = ABS(TableName[Bill] - TableName[Bob])

If Bill and Bob are measures:

Difference = ABS([Bill] - [Bob])

UPDATE: Expression to only calculate difference between Bob and Bill.

Create a measure (in this case DifferenceBillAndBob) and use the following expression.

DifferenceBillAndBob =
ABS (
    SUMX ( FILTER ( Sales, Sales[SalesPerson] = "Bob" ), [SalesValue] )
        - SUMX ( FILTER ( Sales, Sales[SalesPerson] = "Bill" ), [SalesValue] )
)

It is not tested but should work.

Let me know if this helps.

Upvotes: 1

Related Questions