Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Percentage calculation values

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-08-2018
12:44 AM

I am calculating percentage of product values as below.

Prod % Value

A 5%

B 4%

C 10%

D 8%

E 9%

F 3%

G 11%

H 16%

I 18%

other 12%

But when I do Top 5 or exclude other, the percentage values shouldn't change, not sure how to get it done.

%Value is calculated as sum(value)/Total(Value)

Any thought.

Thanks,

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

affan

Solution Sage

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-09-2018
12:11 AM

@Anonymous

Have a look at the attached pbix file which shows the Top 5 applied on average.

**If this helped you, please mark this post as an accepted solution and like to give KUDOS .**

Regards,

Affan

Highlighted

themistoklis

Memorable Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-09-2018
04:29 AM

@Anonymous

Add a filter if you want to exclude product D

Percentage = SUM ( Table[value] ) / CALCULATE ( SUM ( Table[Value] ), All(Table[Prod]), FILTER('Table' , Table[Prod] <> "D") )

10 REPLIES 10

Highlighted
##

AlB

Super User III

Re: Percentage calculation values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-08-2018
01:55 AM

Hi @Anonymous

Can you elaborate a bit more?

Is what you're showing a matrix with Prod on rows and %Value (measure) in values?

What do you mean exactly by * "when I do Top 5 or exclude other" ? *

Highlighted
##

Anonymous

Not applicable

Re: Percentage calculation values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-08-2018
08:48 AM

Thanks AIB for the response, basically above %values shouldn't change when we do Top 5 in filter section or excluing any other Prod lov's.

Any idea.

Highlighted
##

affan

Solution Sage

Re: Percentage calculation values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-08-2018
01:45 PM

Hi @Anonymous

You can acheive this by using ALL() in your measure.

It will be easier to help if you can show some sample data table from which you are creating this report.

It is better to explain the issue like below example.

"My problem is to get the id number separated from a column which shows the data as below."

My actual data is in a column as below

Column1 |

Hello12Monday |

Hello14Tuesday |

The reuslt I need should be

Column1 | ID No |

Hello12Monday | 12 |

Hello14Tuesday | 14 |

This way you can explanin the issue clearly and instead of pasting the screenshots in the question please consider copying the excel table.

I hope this will lead solving the issue fast.

Regards

Affan

Highlighted

affan

Solution Sage

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-09-2018
12:11 AM

@Anonymous

Have a look at the attached pbix file which shows the Top 5 applied on average.

**If this helped you, please mark this post as an accepted solution and like to give KUDOS .**

Regards,

Affan

Highlighted
##

themistoklis

Memorable Member

Re: Percentage calculation values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-09-2018
01:01 AM

@Anonymous

A formual like the following may help you.

Percentage = SUM ( Table[value] ) / CALCULATE ( SUM ( Table[Value] ),ALL( Table[Prod] ) )

Highlighted
##

Anonymous

Not applicable

Re: Percentage calculation values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-09-2018
02:01 AM

Thanks for the reply, but how to restrict the product D in grand total.

Thanks,

Highlighted

themistoklis

Memorable Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-09-2018
04:29 AM

@Anonymous

Add a filter if you want to exclude product D

Percentage = SUM ( Table[value] ) / CALCULATE ( SUM ( Table[Value] ), All(Table[Prod]), FILTER('Table' , Table[Prod] <> "D") )

Highlighted
##

Anonymous

Not applicable

Re: Percentage calculation values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018
12:15 PM

The formula works well, but when I do Top 2 records my percentage values are changing. but in the example it works well, but in real data is getting changed a lot. not sure why??

Highlighted
##

themistoklis

Memorable Member

Re: Percentage calculation values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018
11:06 PM

@Anonymous

You should use SUMX and Values.

Try the following formula:

Percentage = SUMX ( VALUES( Table[Prod] ), CALCULATE ( SUM ( Table[value] ) ) ) / SUMX ( VALUES( Table[Prod] ), CALCULATE ( SUM ( Table[Value] ), All(Table[Prod]), FILTER('Table' , Table[Prod] <> "D") ) )

Announcements

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Visit our Community Blog for articles, guides, and information created by fellow community members.

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors

User | Count |
---|---|

366 | |

129 | |

92 | |

91 | |

91 |

Top Kudoed Authors

User | Count |
---|---|

478 | |

180 | |

179 | |

137 | |

120 |