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
Anonymous
Not applicable

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
Super User

Hi @Anonymous 

 

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
Super User

Hi @Anonymous 

 

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

Anonymous
Not applicable

Perfect!
Thank you 🙂

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.