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
Rod_D
Frequent Visitor

Combine 3 visuals into one using a running percentage?

Hi All, I'm super-new to Power BI and sure could use some help, please.

I'm trying to combine my three visuals below into one: 

 

Rod_D_0-1662159484119.png

Here's a sample table of the data:

Table1   
Process CountA.NameProcess Created DateCalendarYear
1Company A1/23/20182018
5Company A1/24/20182018
7Company A1/29/20182018
4Company A1/30/20182018
1Company A1/31/20182018
10Company A2/5/20182018

 

The "Percentage" is based upon a calculated measure:

"Percentage = CALCULATE(SUM('Table1'[Process Count]),FILTER(ALLSELECTED('Table1'),'Table1'[Calendar Week Ending Date]<=MAX('Table1'[Calendar Week Ending Date])))/CALCULATE(SUM('Table1'[Process Count]),ALLSELECTED('Table1'))"

 

Calendar Week Ending Date = [ProcessCreatedDate] - WEEKDAY(Table1[ProcessCreatedDate],2)+6
Calendar Week Number = WEEKNUM([ProcessCreatedDate])
 
 The visual I need to create needs to show 1) a running percentage of "Process Count" and 2) compare the running percentages year over year.

Below is what I initially came up with, which is not what I need, because 1) I need all

lines to start at 0%, and 2) I need 2018, 2019, and 2020 to end at 100%, just like in each of the separate visuals above.

Rod_D_1-1662159932830.png

 

You're help would be so much appreciated. TIA!

I hope I provided the necessary information to help, and clearly explained what I need.

Kind Regards, Rod

 
 

 

 

 

1 ACCEPTED SOLUTION

@Rod_D Maybe: 

 

Percentage = 
VAR __Year = MAX('Table1'[Calendar Year])
RETURN
CALCULATE(SUM('Table1'[Process Count]),FILTER(ALLSELECTED('Table1'),'Table1'[Calendar Year] = __Year && 'Table1'[Calendar Week Ending Date]<=MAX('Table1'[Calendar Week Ending Date])))/CALCULATE(SUM('Table1'[Process Count]),ALLSELECTED('Table1'),'Table1'[Calendar Year] = __Year)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Rod_D
Frequent Visitor

@Greg_Deckler Thank you for your help and huge step forward! Below is my current visual, based upon your guidance.  

Rod_D_0-1662651607523.png

Is it possible for each calendar year to end at 100% by the end of their calendar year, instead of all the calendar years adding up to 100% (36.8+27.8+21.5+14 = 100%)? I think it's possible, but how to do it is beyond me, at this point. Thanks again. Sincerely. 

@Rod_D Maybe: 

 

Percentage = 
VAR __Year = MAX('Table1'[Calendar Year])
RETURN
CALCULATE(SUM('Table1'[Process Count]),FILTER(ALLSELECTED('Table1'),'Table1'[Calendar Year] = __Year && 'Table1'[Calendar Week Ending Date]<=MAX('Table1'[Calendar Week Ending Date])))/CALCULATE(SUM('Table1'[Process Count]),ALLSELECTED('Table1'),'Table1'[Calendar Year] = __Year)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I think this it! Thank you Thank you Thank you! I just need a couple of days to thoroughly QC my report, and then if everything looks good I will mark this as a solution. Many thanks again!!

Greg_Deckler
Super User
Super User

@Rod_D So the basic issue that you have is that you need to add in a Year filter into your calculation. Like maybe:

"Percentage = 
VAR __Year = MAX('Table1'[Calendar Year])
RETURN
CALCULATE(SUM('Table1'[Process Count]),FILTER(ALLSELECTED('Table1'),'Table1'[Calendar Year] = __Year && 'Table1'[Calendar Week Ending Date]<=MAX('Table1'[Calendar Week Ending Date])))/CALCULATE(SUM('Table1'[Process Count]),ALLSELECTED('Table1'))"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.