cancel
Showing results for
Did you mean:
Helper IV

## How to SUM percentage columns by Week

Hello, everyone,

My table follows this structure:

 Total Zen Week_year Percentage 10 2 2020.01 80% 5 0 2020.01 100% 4 1 2020.01 75% 9 1 2020.08 89% 12 4 2020.14 66% 17 2 2020.14 88%

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:

 Total Zen Week_year Percentage Percentage_ByWeek_year 10 2 2020.01 80% 84% 5 0 2020.01 100% 84% 4 1 2020.01 75% 84% 9 1 2020.08 89% 89% 12 4 2020.14 66% 79% 17 2 2020.14 88% 79%

Can someone help me?

1 ACCEPTED SOLUTION
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

2 REPLIES 2
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

Helper IV

Perfect!
Thank you 🙂

Announcements