Divide by row in Power BI

[absis]

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!

[v-eachen-msft · Accepted solution]

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.