PowerBI - Sum Based On Criteria In Other Rows

Question

I've already accomplished this in excel, yet being new to PowerBI I'm racking my brain here. I have a single table that contains sales data.

Sales Type | Customer | Sales
Cat 1      | Cust 1   | $10000
Cat 1      | Cust 2   | $10000
Cat 1      | Cust 3   | $10000
Cat 1      | Cust 1   | $10000
Cat 2      | Cust 1   | $1000
Cat 2      | Cust 2   | $2000
Cat 2      | Cust 3   | $3000
Cat 2      | Cust 1   | $9000

The output I'm looking for is a ratio between the sales from Category 2 from customers with sales greater than or equal to $20,000 in Category 1, against customers with less than $20,000 in Category 1.

In this example, Cust 1 has $20,000 in sales from Cat 1, and Cust 2 and 3 do not. Customers with sales $20,000 or more in Cat 1, Cat 2 Sales = $10000 Customers with sales less than $20,000 in Cat 1, Cat 2 Sales = $5000

(10000-5000)/5000 = 1.0 or 100% more sales in Cat 2 when Cat 1 sales are $20,000 or more.

I hope this is clear. Thank you in advance!

Aldert Aldert · Accepted Answer · 2019-03-31T11:43:33

First we create a new Table:

SalesPerCat = GROUPBY(Sheet1;Sheet1[ Customer ];Sheet1[Sales Type ];"SumCat";SUMX(CURRENTGROUP();Sheet1[ Sales] ))

Then we add a new Column, Above. This takes over the value when Cat 1 is higher then 20000:

Above = IF(SalesPerCat[Category] = "Cat 2" && LOOKUPVALUE(SalesPerCat[SumCat];SalesPerCat[Customer]; SalesPerCat[Customer];SalesPerCat[Category]; "Cat 1";0) >=20000; SalesPerCat[SumCat];0)

How opposite, new column, for all below 20000:

Below = IF(SalesPerCat[Category] = "Cat 2" && LOOKUPVALUE(SalesPerCat[SumCat];SalesPerCat[Customer]; SalesPerCat[Customer];SalesPerCat[Category]; "Cat 1";0) <20000; SalesPerCat[SumCat];0)

Table result:

Last to make the Ratio measure:

Ratio = (SUM(SalesPerCat[Above]) - SUM(SalesPerCat[Below]))/SUM(SalesPerCat[Below])

PowerBI - Sum Based On Criteria In Other Rows

1 Answers