cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
req7
Helper IV
Helper IV

How to SUM percentage columns by Week

Hello, everyone,

My table follows this structure:

TotalZenWeek_year     Percentage
1022020.0180%
502020.01100%
412020.0175%
912020.0889%
1242020.1466%
1722020.1488%


I created a column in my table that divides the value of the "Zen" column by the value of the "Total" column and subtracts 1 from the final result. This is what the column "Percentage" do.    *  1-(DIVIDE(MyTable[Zen],MyTable[Total]) 

 

But now I want a column that does the same thing as a "Percentage" column, but for all the table. The column must sum up all the values ​​in the "Zen" column and divide by the sum of all the values ​​in the "Total" column and subtract 1 from the final result, BUT in a way that this results is separated by each different "Week_year".To exemplify, the final result should look like this:

 

TotalZenWeek_year  Percentage      Percentage_ByWeek_year
1022020.0180%84%
502020.01100%84%
412020.0175%84%
912020.0889%89%
1242020.1466%79%
1722020.1488%79%


Can someone help me?

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

Hi @req7 

 

Percentage_ByWeek_year =
VAR __total =
    CALCULATE ( SUM ( Table1[Total] ), ALLEXCEPT ( Table1, Table1[Week_year] ) )
VAR __zen =
    CALCULATE ( SUM ( Table1[Zen] ), ALLEXCEPT ( Table1, Table1[Week_year] ) )
RETURN
    1 - DIVIDE ( __zen, __total )

 

or alternatively

Percentage_ByWeek_year = 
1 - CALCULATE(DIVIDE(SUM(Table1[Zen]), SUM(Table1[Total])),ALLEXCEPT(Table1,Table1[Week_year]))

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

2 REPLIES 2
AlB
Super User III
Super User III

Hi @req7 

 

Percentage_ByWeek_year =
VAR __total =
    CALCULATE ( SUM ( Table1[Total] ), ALLEXCEPT ( Table1, Table1[Week_year] ) )
VAR __zen =
    CALCULATE ( SUM ( Table1[Zen] ), ALLEXCEPT ( Table1, Table1[Week_year] ) )
RETURN
    1 - DIVIDE ( __zen, __total )

 

or alternatively

Percentage_ByWeek_year = 
1 - CALCULATE(DIVIDE(SUM(Table1[Zen]), SUM(Table1[Total])),ALLEXCEPT(Table1,Table1[Week_year]))

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

Perfect!
Thank you 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors