cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## % of Total in Pivot Table

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]))

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.