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
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?
Solved! Go to 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
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
Perfect!
Thank you 🙂
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.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |