How do I create a Rolling Total and Percentage of Total based on multiple columns?

[burdenCondo]

I have a table as follows:

Team Company	Team Sub-Company	Enrollment Status	Enrolled Since	Tag
A	AA	Enrolled	2023-01-01	123
A	AB	Enrolled	2023-01-02	123 || 456
B	BA	Not Enrolled
C	CA	Not Enrolled		789

The table shows the Enrollment Status to a program of Teams, which have other metadata such as Company, Sub-Company, Tags, etc. The "Enrollment Status" column indicates whether the team enrolls to a program or not, and if they are, then it has a column "Enrolled Since" as date.

I want to create 4 new columns:

• "Enrolled Count so far by Company"
• "Enrolled Percentage so far by Company"
• "Enrolled Count so far by Sub-Company"
• "Enrolled Percentage so far by Sub-Company"

As you see, the last two columns are just the former two columns, but using a different team. This is what I am looking for:

Team Company	Team Sub-Company	Enrollment Status	Enrolled Since	Tag	Enrolled Count so far by Company	Enrolled Percentage so far by Company	Enrolled Count so far by Sub-Company	Enrolled Percentage so far by Sub-Company
A	AA	Enrolled	2023-01-01	123	1	50%	1	100%
A	AB	Enrolled	2023-01-02	456	2	100%	1	100%
B	BA	Not Enrolled			1	0%	1	100%
C	CA	Not Enrolled		789	1	0%	1	100%

I can easily calculate that using a Native database query (in my case, Snowflake). However, what I am unable to make it right is when I apply the filters. I observe that the query calculates the values first to get the data, then when the filter is applied (e.g., I filter rows when tag contains "123" only), the new values are NOT re-calculated. This is what I get when I use that tag filter:

Team Company	Team Sub-Company	Enrollment Status	Enrolled Since	Tag	Enrolled Count so far by Company	Enrolled Percentage so far by Company	Enrolled Count so far by Sub-Company	Enrolled Percentage so far by Sub-Company
A	AA	Enrolled	2023-01-01	123	1	50%	1	100%

As you can see, the filter does not trigger the re-calculation, which it should. 

My question:

• Is it possible to apply filter first, then do calculations via the Measure/DAX/whatever it is by PowerBI?
• If yes, then how should I write the Measure/DAX queries?

[rubayatyasmin]

Hi, @burdenCondo 

You can apply filter first then create measure in PBI. 

you can try below code for after filtering. 

Enrolled Count by Company =
CALCULATE(
COUNT('Table'[Enrollment Status]),
'Table'[Enrollment Status] = "Enrolled",
ALLEXCEPT('Table', 'Table'[Team Company])
)

Enrolled Percentage by Company =
DIVIDE(
[Enrolled Count by Company],
CALCULATE(
COUNT('Table'[Enrollment Status]),
ALLEXCEPT('Table', 'Table'[Team Company])
),
0
)

It should work. Please make the necessary changes in DAX. 

If my assistance helped you in any way, hit 👍.