Forum Discussion

absis's avatar
absis
New Member
7 years ago
Solved

Divide by row in Power BI


Hi guys,

I've been having a little problem doing something in the matrix.

 

I have the following table

Product            Price         Quantity

AA                   1000           50

BB                    2000          40

CC                   3000           30
DD                  4000           20

 

I need to divide by row and also add more new row like Ratio_CCDD, CC/DD get a result for a row of CC divided by a row of DD product.

The result in Maxtrix should follow:


Product                  Price          Quantity            Amount

AA                         1000            50                      50000

BB                          2000            40                      80000

CC                         3000            30                       90000
Ratio_CCDD          0.75             1.5                      1.125

DD                        4000             20                      80000

 

How can I solve this?

 

Cheers!

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    Hi absis ,

     

    At first, you need a calculated column “Amount”.

    Amount =
'Table'[Price] * 'Table'[Quantity]

    According to the description of your problem, I divide CC by DD. The result will appear in a new table “Table 2”.

    Table 2 =
ROW (
    "Product", "Ratio_"
        & CALCULATE (
            SELECTEDVALUE ( 'Table'[Product] ),
            FILTER ( 'Table', 'Table'[Product] = "CC" )
        )
        & CALCULATE (
            SELECTEDVALUE ( 'Table'[Product] ),
            FILTER ( 'Table', 'Table'[Product] = "DD" )
        ),
    "Price", DIVIDE (
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Price] ),
            FILTER ( 'Table', 'Table'[Product] = "CC" )
        ),
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Price] ),
            FILTER ( 'Table', 'Table'[Product] = "DD" )
        )
    ),
    "Quantity", DIVIDE (
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Quantity] ),
            FILTER ( 'Table', 'Table'[Product] = "CC" )
        ),
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Quantity] ),
            FILTER ( 'Table', 'Table'[Product] = "DD" )
        )
    ),
    "Amount", DIVIDE (
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Amount] ),
            FILTER ( 'Table', 'Table'[Product] = "CC" )
        ),
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Amount] ),
            FILTER ( 'Table', 'Table'[Product] = "DD" )
        )
    )
)

    Then use UNION() function to combine two tables into one table “Table 3”.

     

    Table 3 =
UNION ( 'Table', 'Table 2' )

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi absis ,

     

    At first, you need a calculated column “Amount”.

    Amount =
'Table'[Price] * 'Table'[Quantity]

    According to the description of your problem, I divide CC by DD. The result will appear in a new table “Table 2”.

    Table 2 =
ROW (
    "Product", "Ratio_"
        & CALCULATE (
            SELECTEDVALUE ( 'Table'[Product] ),
            FILTER ( 'Table', 'Table'[Product] = "CC" )
        )
        & CALCULATE (
            SELECTEDVALUE ( 'Table'[Product] ),
            FILTER ( 'Table', 'Table'[Product] = "DD" )
        ),
    "Price", DIVIDE (
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Price] ),
            FILTER ( 'Table', 'Table'[Product] = "CC" )
        ),
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Price] ),
            FILTER ( 'Table', 'Table'[Product] = "DD" )
        )
    ),
    "Quantity", DIVIDE (
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Quantity] ),
            FILTER ( 'Table', 'Table'[Product] = "CC" )
        ),
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Quantity] ),
            FILTER ( 'Table', 'Table'[Product] = "DD" )
        )
    ),
    "Amount", DIVIDE (
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Amount] ),
            FILTER ( 'Table', 'Table'[Product] = "CC" )
        ),
        CALCULATE (
            SELECTEDVALUE ( 'Table'[Amount] ),
            FILTER ( 'Table', 'Table'[Product] = "DD" )
        )
    )
)

    Then use UNION() function to combine two tables into one table “Table 3”.

     

    Table 3 =
UNION ( 'Table', 'Table 2' )

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.