% of Total in Pivot Table

[brent_excel]

Hello,

I am trying to figure out how to get the % of total in a power pivot table.

For Item A, I would like to divide 5 / 15 = 33% , and 10/15 = 67%, for each of the "No" and "Yes" columns.  Then repeat for each of the items.

I have tried a few calculations, but cannot seem to get it to work.

=CALCULATE(SUM ('Table1'[Units]),FILTER (('Table1' ),'Table1'[On Display] = "Yes")) / CALCULATE(SUM ('Table1'[Units]),FILTER (ALL('Table1' ),'Table1'[On Display]))

[v-rongtiep-msft · Accepted solution]

Hi @brent_excel ,

Please refer to my pbix file to see if it helps you.

Create a measure.

value_percent =
VAR _sumall =
    CALCULATE (
        SUM ( 'Table'[Units] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[row label] = SELECTEDVALUE ( 'Table'[row label] )
        )
    )
VAR _yes =
    (
        CALCULATE (
            SUM ( 'Table'[Units] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[row label] = SELECTEDVALUE ( 'Table'[row label] )
                    && 'Table'[On display] = "Yes"
            )
        )
    ) / _sumall
VAR _no =
    (
        CALCULATE (
            SUM ( 'Table'[Units] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[row label] = SELECTEDVALUE ( 'Table'[row label] )
                    && 'Table'[On display] = "No"
            )
        )
    ) / _sumall
RETURN
    IF (
        MAX ( 'Table'[On display] ) = "Yes",
        _yes,
        IF ( MAX ( 'Table'[On display] ) = "No", _no, BLANK () )
    )

Or a column.

Column =
VAR _sumall =
    CALCULATE (
        SUM ( 'Table'[Units] ),
        FILTER ( ( 'Table' ), 'Table'[row label] = EARLIER ( 'Table'[row label] ) )
    )
VAR _yes =
    (
        CALCULATE (
            SUM ( 'Table'[Units] ),
            FILTER (
                ( 'Table' ),
                'Table'[row label] = EARLIER ( 'Table'[row label] )
                    && 'Table'[On display] = "Yes"
            )
        )
    ) / _sumall
VAR _no =
    (
        CALCULATE (
            SUM ( 'Table'[Units] ),
            FILTER (
                ( 'Table' ),
                'Table'[row label] = EARLIER ( 'Table'[row label] )
                    && 'Table'[On display] = "No"
            )
        )
    ) / _sumall
RETURN
    IF (
        'Table'[On display] = "Yes",
        _yes,
        IF ( 'Table'[On display] = "No", _no, BLANK () )
    )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.