cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Percentage calculation with Filtered Context

Hi all,

I would like to check if you guys could help assist on quick calculation in the Data Model.

I would like to show these quantities as percentage so I can create a line chart for it.

 Time_Stamp On Time Within 2 weeks Within 4 weeks > 4 weeks Total 2017.01 563358 63517 28581 13066 668522 2017.02 623668 72048 30393 17773 743882 2017.03 684312 89520 31758 21748 827338 2017.04 710722 101539 51280 24384 887925 2017.05 763081 105043 51280 28392 947796

So far here is what I wrote in the modelling:

Sum Ordered Qty = SUM(Query1[Ordered quantity])

Total Ordered Qty = CALCULATE([Sum Ordered Qty],ALL(Query1[Ordered quantity] ))

% Ordered Qty = [Sum Ordered Qty]/[Total Ordered Qty]

But this gave me percentage value of 100% for all.

Desired Data result to build a line chart:

 Time_Stamp On Time Within 2 weeks Within 4 weeks > 4 weeks Total 2017.01 84% 10% 4% 2% 100% 2017.02 84% 10% 4% 2% 100% 2017.03 83% 11% 4% 3% 100% 2017.04 80% 11% 6% 3% 100% 2017.05 81% 11% 5% 3% 100%

Regards

Yatz

4 REPLIES 4
Super User

Hi @Anonymous,

The best way would be to change the format of your  table and unpivot the colums and remove the Total colum so you would have something like this:

Time_StampAttributeValue

 01/01/2017 On Time 563358 01/01/2017 Within 2 weeks 63517 01/01/2017 Within 4 weeks 28581 01/01/2017 > 4 weeks 13066 01/02/2017 On Time 623668 01/02/2017 Within 2 weeks 72048 01/02/2017 Within 4 weeks 30393 01/02/2017 > 4 weeks 17773 01/03/2017 On Time 684312 01/03/2017 Within 2 weeks 89520 01/03/2017 Within 4 weeks 31758 01/03/2017 > 4 weeks 21748 01/04/2017 On Time 710722 01/04/2017 Within 2 weeks 101539 01/04/2017 Within 4 weeks 51280 01/04/2017 > 4 weeks 24384 01/05/2017 On Time 763081 01/05/2017 Within 2 weeks 105043 01/05/2017 Within 4 weeks 51280 01/05/2017 > 4 weeks 28392

This would allow much less measures but taking your format into account you should add the measures below and format them in % then add them to you charts instead of the actual quantities.

```On_Time = DIVIDE(SUM(Table1[On Time]);SUM(Table1[Total]))

Within_2_weeks = DIVIDE(SUM(Table1[Within 2 weeks]);SUM(Table1[Total]))

Within_4_weeks = DIVIDE(SUM(Table1[Within 4 weeks]);SUM(Table1[Total]))

> 4week = DIVIDE(SUM(Table1[> 4 weeks]);SUM(Table1[Total]))

Total_ = DIVIDE(SUM(Table1[Total]);SUM(Table1[Total]))```

Regards,

Mfelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Microsoft Employee

Hi @Anonymous,

Could you try the formula below(use ALL(Query1) instead of ALL(Query1[Ordered quantity] ) as the filter) for "Total Ordered Qty" measure to see if it works?

`Total Ordered Qty = CALCULATE ( [Sum Ordered Qty], ALL ( Query1 ) )`

Regards

Anonymous
Not applicable

Tried the suggestion, bu unfortunately, doesnt work.

Microsoft Employee

Hi @Anonymous,

My mistake!

Please try the formula below to see if it works.

`Total Ordered Qty = CALCULATE ( [Sum Ordered Qty], ALLEXCEPT ( Query1, Query1[Time_Stamp] ) )`

Regards