cancel
Showing results for
Did you mean: Anonymous
Not applicable

Percentage calculation values

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,

2 ACCEPTED SOLUTIONS

Accepted Solutions
affan Established Member

Re: Percentage calculation values

@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

themistoklis New Contributor

Re: Percentage calculation values

@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 Super User

Re: Percentage calculation values

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" ? Anonymous
Not applicable

Re: Percentage calculation values

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.

affan Established Member

Re: Percentage calculation values

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

affan Established Member

Re: Percentage calculation values

@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 New Contributor

Re: Percentage calculation values

@Anonymous

A formual like the following may help you.

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

Re: Percentage calculation values

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

Thanks,

themistoklis New Contributor

Re: Percentage calculation values

@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") ) Anonymous
Not applicable

Re: Percentage calculation values

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??

themistoklis New Contributor

Re: Percentage calculation values

@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 Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 109 members 1,460 guests
Recent signins:
• • rg325 • JOO13 • AiolosZhao • mcybulski • Mr_E • sfog • JessO • franciscoalopez • Vamshi_R • suddin • abnerfc01 • seacubs17 • qbaenkhbat 