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: Order SKU Quantity 1 Banana 10 1 Apple 5 2 Banana 10 2 Appl...
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

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.