cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Community Support

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.

Community Support

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors