Forum Discussion

Sparticus2023's avatar
Sparticus2023
Copper Contributor
Aug 21, 2023

Calculating percent based on values appearing in another column (PowerPivot)

I have an Excel data model that uses a few PowerQueries then outputs to PowerPivots. My issue is trying to solve for a couple measures that I can't seem to figure out.

 

First, I need a measure that displays values from Table B (column F in the screenshot below) only if the Pivot is displaying values in the Pivot which are from table A (column D).

 

Second, I then need a measure to calculate the percentage of the amount in the row compared to the total amount in the first measure (for each bucket). This is column G in the screenshot below.

 

I have tried CALCUATE, IF, and SUMX in many variations but cant seem to figure it out.

 

Thank you

BI & Data Analysis
power bi

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 21, 2023

    Sparticus2023 

    For such sample

    it could be

    Actuals, $ :=
SUM ( Table1[Actuals Allocated] )

Actuals, % :=
VAR subtotal =
    CALCULATE ( [Actuals, $], ALL ( Table1[Category] ) )
RETURN
    DIVIDE ( IF ( ISBLANK ( [Actuals, $] ), 0, [Actuals, $] ), subtotal )

Amount, $ :=
SUMX (
    Table1,
    Table1[Driver Amount]
        * IF ( ISBLANK ( Table1[Actuals Allocated] ), BLANK (), 1 )
)

Amount, % :=
VAR subtotal =
    CALCULATE ( [Amount, $], ALL ( Table1[Category] ) )
RETURN
    DIVIDE ( IF ( ISBLANK ( [Amount, $] ), 0, [Amount, $] ), subtotal )
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 21, 2023

    Sparticus2023 

    It sounds like you want to create Power BI measures that involve conditional calculations based on values in different tables. Here is how you can approach these two measures:

    1. Display Values from Table B Based on Table A:

    To display values from Table B (column F) only if the Pivot is displaying values from Table A (column D), you can use the following DAX measure:

    FilteredValues = CALCULATE( MAX('Table B'[Column F]), 'Table A'[Column D] = SELECTEDVALUE('Table A'[Column D]) )

    In this measure, 'Table B'[Column F] is filtered based on the value selected in 'Table A'[Column D].

    1. Calculate Percentage of Row Amount to Total Amount:

    To calculate the percentage of the amount in the row compared to the total amount in the first measure, you can use the following DAX measure:

    PercentageOfTotal = DIVIDE( [FilteredValues], SUM('Table B'[Column F]), BLANK() )

    In this measure, [FilteredValues] is the measure you calculated in the first step. SUM('Table B'[Column F]) calculates the total amount from Table B. The DIVIDE function calculates the percentage, and BLANK() handles scenarios where the total amount is zero.

    Remember to replace 'Table A'[Column D], 'Table B'[Column F], 'Table B', and any other column/table names with your actual column/table names.

    Apply these measures in your Power Pivot data model, and they should provide you with the desired results. Remember that Power BI measures work based on context, so their behavior might vary depending on the context in which they are displayed.

    Since no one has answered it for at least one day, I entered your question in various AI. The text and the steps were created with the help of AI.

    My answers are voluntary and without guarantee!

    Hope this will help you.

Resources