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 tha...
BI & Data Analysis
power bi
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 )

Resources