How to SUM percentage columns by Week

[Anonymous]

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?

[AlB · Accepted solution]

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 

[Anonymous]

Perfect!
Thank you 🙂