cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Solution Sage
Solution Sage

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

View solution in original post

Highlighted
Memorable Member
Memorable Member

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

 

 

View solution in original post

10 REPLIES 10
Highlighted
Super User III
Super User III

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

Highlighted
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.

Highlighted
Solution Sage
Solution Sage

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

Column1ID No
Hello12Monday12
Hello14Tuesday14

 

 

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
Solution Sage
Solution Sage

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

View solution in original post

Highlighted
Memorable Member
Memorable Member

Re: Percentage calculation values

@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

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

 

Thanks,

Highlighted
Memorable Member
Memorable Member

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

 

 

View solution in original post

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

Highlighted
Memorable Member
Memorable Member

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors