Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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