Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate between same field

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
ROWSYTD
SALES1.000
BONUS-300
SALARY-400

 

 

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

 

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    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