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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
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_StampOn TimeWithin 2 weeksWithin 4 weeks> 4 weeksTotal
2017.01563358635172858113066668522
2017.02623668720483039317773743882
2017.03684312895203175821748827338
2017.047107221015395128024384887925
2017.057630811050435128028392947796

 

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_StampOn TimeWithin 2 weeksWithin 4 weeks> 4 weeksTotal
2017.0184%10%4%2%100%
2017.0284%10%4%2%100%
2017.0383%11%4%3%100%
2017.0480%11%6%3%100%
2017.0581%11%5%3%

100%

 

 

Regards

Yatz

4 REPLIES 4
MFelix
Super User
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/2017On Time563358
01/01/2017Within 2 weeks63517
01/01/2017Within 4 weeks28581
01/01/2017> 4 weeks13066
01/02/2017On Time623668
01/02/2017Within 2 weeks72048
01/02/2017Within 4 weeks30393
01/02/2017> 4 weeks17773
01/03/2017On Time684312
01/03/2017Within 2 weeks89520
01/03/2017Within 4 weeks31758
01/03/2017> 4 weeks21748
01/04/2017On Time710722
01/04/2017Within 2 weeks101539
01/04/2017Within 4 weeks51280
01/04/2017> 4 weeks24384
01/05/2017On Time763081
01/05/2017Within 2 weeks105043
01/05/2017Within 4 weeks51280
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



v-ljerr-msft
Employee
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?Smiley Happy

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

 

Regards

Anonymous
Not applicable

Hi @v-ljerr-msft,

 

Tried the suggestion, bu unfortunately, doesnt work.

 

Capture.JPG

Hi @Anonymous,

 

My mistake!

 

Please try the formula below to see if it works.Smiley Happy

 

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

 

 

Regards

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.