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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.