Percentage calculation with Filtered Context

[Anonymous]

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

[MFelix]

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

[v-ljerr-msft]

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]

Hi @v-ljerr-msft,

Tried the suggestion, bu unfortunately, doesnt work.

[v-ljerr-msft]

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