Calculate between same field

[Anonymous]

I have a table, ORIGINAL_TABLE. The table has ROWS and YTD fields. In the table in the field ROWS I have the following records Sales, bonus and salary. I want to create a table in power bi that in addition to these records also have some kpis, in which case the kpis would be bonus / sales and salary / sales. Below we can see how it is and how it would like it to be.

ORIGINAL TABLE
ROWS	YTD
SALES	1.000
BONUS	-300
SALARY	-400

NEW TABLE
KPI	YTD
SALES	1.000
BONUS	-300
SALARY	-400
BONUS / SALES	-30%
SALARY / SALES	-40%

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create calculated table using following DAX to meet your requirement.

KPITable = 
UNION (
    'Table2',
    ROW (
        "ROWS", "BONUS/SALES",
        "YTD", DIVIDE (
            LOOKUPVALUE ( 'Table2'[YTD], 'Table2'[Rows], "BONUS" ),
            LOOKUPVALUE ( 'Table2'[YTD], 'Table2'[Rows], "SALES" )
        )
    ),
    ROW (
        "ROWS", "SALARY/SALES",
        "YTD", DIVIDE (
            LOOKUPVALUE ( 'Table2'[YTD], 'Table2'[Rows], "SALARY" ),
            LOOKUPVALUE ( 'Table2'[YTD], 'Table2'[Rows], "SALES" )
        )
    )
)

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Nathaniel_C]

Hi @Anonymous ,

Not sure what your question is, would you please be more specific?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel