Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lasthero
Frequent Visitor

Measures for % of grand total and % of a column

Hi all,


Was hoping someone could help me out here.  Assuming i have the following data in sheet1:

 

VendorIditemsValidReason
Ven111YReason1
Ven112YReason1
Ven221NReason2
Ven331YReason1
Ven332NReason2
Ven333NReason2
Ven441YReason3
Ven442YReason3
Ven451NReason2
Ven561N

Reason1

 

 

 

How would I create a measure that is the % of total based on items and validity?

 

For example if I have a pie chart displayed of all the reasons, and then I have a slicer for Valid and Vendor.

What I want to create is two number % visuals that will change based off of the slicers.

 

So for example if I click the slicer and I select Ven4 and click the other slicer and valid = Y, I want one % visual to display the count of ven4 items vs all so 30%(3/10). And the 2nd % visual I want to display % of valid only for ven4. So 66%(2/3). Sorry if this sounds confusing I will try to clarify if needed.

 

Thanks in advance!

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@lasthero All these are MEASURES!

 

Transactions = COUNTROWS ('Table')

Transactions (Overall) = CALCULATE ( [Transactions], ALL('Table'))

Transactions (Overall by Vendor) = CALCULATE ( [Transactions], ALLEXCEPT('Table', 'Table'[Vendor]))

Transactions (Valid) = CALCULATE ( [Transactions], FILTER ( ALLSELECTED('Table'), 'Table'[Valid]="Y"))

MEASURE 1 = DIVIDE ( [Transactions (Overall by Vendor)], [Transactions (Overall)], 0 )

MEASURE 2 = DIVIDE ( [Transactions (Valid)], [Transactions (Overall by Vendor)], 0 )

I think this is what you are trying to do???

 

2016-10-06 - Percentages.png

 

Hope this helps! Smiley Happy

View solution in original post

1 REPLY 1
Sean
Community Champion
Community Champion

@lasthero All these are MEASURES!

 

Transactions = COUNTROWS ('Table')

Transactions (Overall) = CALCULATE ( [Transactions], ALL('Table'))

Transactions (Overall by Vendor) = CALCULATE ( [Transactions], ALLEXCEPT('Table', 'Table'[Vendor]))

Transactions (Valid) = CALCULATE ( [Transactions], FILTER ( ALLSELECTED('Table'), 'Table'[Valid]="Y"))

MEASURE 1 = DIVIDE ( [Transactions (Overall by Vendor)], [Transactions (Overall)], 0 )

MEASURE 2 = DIVIDE ( [Transactions (Valid)], [Transactions (Overall by Vendor)], 0 )

I think this is what you are trying to do???

 

2016-10-06 - Percentages.png

 

Hope this helps! Smiley Happy

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.