Forum Discussion

jppasini's avatar
jppasini
New Member
6 years ago
Solved

How to divide two different rows in a table

Hello everybody!

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

OrderSKUQuantity
1

Banana

10
1

Apple

5
2Banana10
2Apple1
3Banana10
3Apple5
4Banana10
5Apple2

 

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?

Need Help
  • CoreyP's avatar
    CoreyP
    6 years ago

    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

3 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

  • CoreyP's avatar
    CoreyP
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.