How to divide two different rows in a table

[jppasini]

Hello everybody!

I´m bogged, trying to solve a simple problem. I have a "Sales" table with info such as this:

Order	SKU	Quantity
1	Banana	10
1	Apple	5
2	Banana	10
2	Apple	1
3	Banana	10
3	Apple	5
4	Banana	10
5	Apple	2

I need to generate a grand total for Bananas, a grand total for Apples, and then divide Apples/Banannas.

In this example:

GT Bananas = 40

GT Apples = 13

Ratio = 13 / 40 = 32,5%

I use this method to compare the sales of a "weak" product (apples) vs the sales of a "strong" product (bananas) and to find new opportunities to cross-sell.

Any helping hand/brain?

[v-juanli-msft]

Hi @jppasini 

Create measures

total per sku = CALCULATE(SUM('Table'[Quantity]),ALLEXCEPT('Table','Table'[SKU]))

weak/strong =
VAR min_ =
    MINX ( ALL ( 'Table'[SKU] ), [total per sku] )
VAR max_ =
    MAXX ( ALL ( 'Table'[SKU] ), [total per sku] )
RETURN
    SWITCH ( [total per sku], min_, "weak", max_, "strong" )

ratio =
CALCULATE (
    [total per sku],
    FILTER ( ALLSELECTED ( 'Table' ), [weak/strong] = "weak" )
)
    / CALCULATE (
        [total per sku],
        FILTER ( ALLSELECTED ( 'Table' ), [weak/strong] = "strong" )
    )

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

[CoreyP · Accepted solution]

Measure 1:

GT Apples = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Apples" )

Measure 2:

GT Bananas = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Bananas" )

Measure 3:

Apple % of Banana = [GT Apples] / [GT Bananas]

Or, you could combine into one measure like this:

Apple % of Banana = 

VAR 

GT_Apples = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Apples" )

GT_Bananas = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Bananas" )

RETURN

GT_Apples / GT_Bananas

[az38]

hi @jppasini 

try a measure

Measure = divide(calculate(sum('Table1'[Quantity]);ALL('Table1');'Table1'[SKU]="Banana");calculate(sum('Table1'[Quantity]);ALL('Table1');'Table1'[SKU]="Apple"))

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn