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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate percentage based on another measure percentage

Hello,

Maybe somebody can help me. I´m trying to calculate the percentage of the turnover that fills out certain conditions with the total of incomes.

 

For doing this I have a column with all the turnover and another column with different conditions. 

What I want is to sum up the turnover for the rows who meet these conditions and find the average of the total.

 

Can somebody help me?

 

Thank you

A. G. 

14 REPLIES 14
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Please share a dummy sample.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello,

I sent the example.

In the first image you will find a chart with total values.

 

power2.PNG

 

The second file enclosed is a chart based on the previous one, but filtered

 

power1.PNG

 

And what I´m looking for is a way to calculate the percentage for each value related to the the total.

I can´t figure out a way to do this.

 

Thank you

Best Regards

A.G.

 

Anonymous
Not applicable

Thank you for your reply,

but I think I have explain me wrong. Technically what I need is to divide the result of a filter concerning the same amount without filtering.

I the example I want a way to divide:

Mes1 Prima 2016 (Total amount) / Mes1 Prima 2016 (filtered amount)

or what is the same numerical:

 

86.369,60 / 198.821,96 

 

to find:

 

0,4344

or:

43,44%

 

Thank you

Best Regards

A.G.

Hi,

 

Ok, could you please replace all with allselected? That'll do i hope.

Anonymous
Not applicable

Do you mean to change:

 

All formula= Calculate((total 2018), all (mes))

 

to

 

All formula= Calculate((total 2018), allselected (mes))?

 

I´ve tried to do it,, but the output is the same.

 

Thank you!

Yes, with all selected formula, you need to apply a filter and choose the years or months to be applied. If you want to see the 2016/(2017+2018) you need to select 2017 and 2018 in a filter.

Anonymous
Not applicable

Hello Pbiozkan,

thank you for your support!!!

Smiley Wink

 

But something is not working.

 

The equation is very simple

 

X= (A-B)/A

 

In this case "A "should be the total amount "Prima 2016" 

and "B" what was filtered in the previuos visualization 

 

So for example for 2016 the complete year should become the next output:

 

0.3851 = (3.555.318,73 - 2.185.858,60) / 3.555.318,73

 

What do you think?

Best Regards

A.G.

Hi,

 

if you want to find the percentage of 2 in 5, you should use 2/5 directly. However, if you'd like to see the drop or increase; 2-5/5. 

 

So, if you'd like to see the increase/decrease percentage in 2018, you should use 2018 value-2017 value/ 2018 value

 

if you'd like to see "what does 2017 sales mean in my total sales" you should use 2017/all sales

 

Hope that helps

Anonymous
Not applicable

Hello,

I´m not comparing sales of different years. I´m comparing sales in the same year, but earned in a specific distribution channel.

Imagine that you have a total amount of 1000 euros.

 

200 Euros through website direct sales

200 Euros through phone direct sales

and

600 Euros through dealers indirect sales

 

Now I create a visualization only with direct sales (excluding with a filter in the visualization indirect sales)

and I want to include in this visualization the % that represent this 400 euros compared to the total amount of 1000.

 

Something like this:

 

Amount  Perc.

400         40%

 

Thank you

Yes, then you should write your formula as below;

 

A/B instead of A-B/A 🙂

 

 

Anonymous
Not applicable

Yes,

the point is how can I do this in PowerBI,

because if I divide A/B I will become 1

 

Measure = Prima 2016/Prima 2016 

Measure = 1

 

my problem is that I have to include the filters in the equation and I don´t know how to do this.

 

ok;

 

those formulas from previous post:

 

1. Total 2018= sum(prima 2018)

2. Total 2017= sum(prima 2017)

3. Total 2016= sum(prima 2016)

 

now we have sum of all years individually. add totals to your table. put a slicer for years and select 2018.

 

then, add another measure;

 

Allselected2018=Calculate((total 2018), allselected (mes))

 

pct of all selected= total 2018/ allselected2018

Hi,

 

You can use below and if you'd apply filter, it will calculate percentage with filter;

 

 

First, add some total sums;

 

1. Total 2018= sum(prima 2018)

2. Total 2017= sum(prima 2017)

3. Total 2016= sum(prima 2016)

 

if you need total of 3 years add Total 2018 + Total 2017 T Total 2016 and name it as Ultimate Total

 

4. All formula= Calculate((total 2018), all (mes))

 

5. All Mes= Total 2018/All formula

 

or find percentage for 3 years=

 

Ultimate Total Percentage= Total 2018/ Ultimate Total or,

                                             Total 2017/ Ultimate Total or,

                                             Total 2016/ Ultimate Total

 

 

 

pbiozkan
Helper I
Helper I

Hi,

 

Please share your 2 columns so I can help with your formula.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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