Need conditional Count/Running Total from a Data Set

[kamran]

Hi Gurus

I'm new to Power BI and we are in transition planning from Excel Pivots to Power BI dashboards. I've been given a test case to check the compatibility of Power BI with our scenario. I've following data (which is pulled from SSAS cubes):

AgeBand	ProgramYear	ProgramCode	Trimester	StudentNumber
15-20	2015		Psychology		T1	111111
15-20	2015		Psychology		T1	111112
15-20	2015		Psychology		T1	111113
21-25	2015		Psychology		T1	111114
21-25	2015		Psychology		T1	111115
21-25	2015		Psychology		T1	111116
26-30	2015		Psychology		T1	111117
26-30	2015		Psychology		T1	111118
26-30	2015		Psychology		T1	111119
15-20	2015		Psychology		T2	111110
15-20	2015		Psychology		T2	111112
15-20	2015		Psychology		T2	111113
21-25	2015		Psychology		T2	111121
21-25	2015		Psychology		T2	111122
21-25	2015		Psychology		T2	111115
21-25	2015		Psychology		T2	111116
26-30	2015		Psychology		T2	111117
26-30	2015		Psychology		T2	111118
26-30	2015		Psychology		T2	111119
15-20	2015		Psychology		T3	111123
15-20	2015		IT			T1	111111
15-20	2015		IT			T2	111111
15-20	2015		IT			T3	111111
15-20	2015		IT			T3	111112

From this data I need two kinds of outputs.

First one is:

Psychology
Year	2015
Trimester	T1	T2	T3	Psychology Total
AgeBand	New Enrolments	Re-Enrolments	Total Enrolments	New Enrolments	Re-Enrolments	Total Enrolments	New Enrolments	Re-Enrolments	Total Enrolments	New Enrolments	Re-Enrolments	Total Enrolments
15-20	3	0	3	1	2	3	1	2	3	5	4	9
21-25	3	0	3	2	2	3	0	0	0	5	2	7
26-30	3	0	3	0	3	3	0	0	0	3	3	6

I am planning to build this output throuh Matrix layout by having Trimesters in columns whereas user will select Year & Program from Slicers. But I can't calculate New & Re-Enrolments figures in Power BI.

The second output I need is:

Year	Program	Trimester	Age	New Enrolments	New Running Total	Re-Enrolments	Re-Enrolments Running Total
2015	Psychology	T1	15-20	3	3	0	0
2015	Psychology	T1	21-25	3	6	0	0
2015	Psychology	T1	26-30	3	9	0	0
2015	Psychology	T2	15-20	1	10	2	2
2015	Psychology	T2	21-25	2	12	2	4
2015	Psychology	T2	26-30	0	12	3	7
2015	Psychology	T3	15-20	1	13	2	9
2015	IT	T1	15-20	1	1	0	0
2015	IT	T2	15-20	0	1	1	1
2015	IT	T3	15-20	1	2	1	2

Same problem I've in this output is to differentiate between New & Re-Enrolment fiures and then to have their runnings totals.

Can you please help me to build up this report?

[v-huizhn-msft]

Hi @kamran,

>>But I can't calculate New & Re-Enrolments figures in Power BI.

After research, I am still confusing how to calculate the New and Re-Enrolments. For example, in output table2, how to get 1 in 15-20 age, T2Trimester, 'Psychology'ProgramCode, 2015?

In addition, in Excel Pivot, you get the result by creating calculated or measure? You can also create calculated column and measure in Power BI.

Could you please share more details for further analysis?

Best Regards,
Angelia

[kamran]

Hi Angelia

Thanks a lot for looking into my problem.

>>After research, I am still confusing how to calculate the New and Re-Enrolments. For example, in output table2, how to get 1 in 15-20 age, T2Trimester, 'Psychology'ProgramCode, 2015?

New Enrolment means, any student appearing for the first time in a program (Psychology or IT) in our data set is New. If it appears 2nd or 3rd time in subsequent years or trimesters but in the same program, it should be considered as Re-Enroling student.

>> In addition, in Excel Pivot, you get the result by creating calculated or measure? You can also create calculated column and measure in Power BI.

I've generated this output with SQL and I tried to generate BI report with Advanced option of Direct Query, but there are limitations on using SQL query as a data source e.g use of Common Table Expression in the Query, application of Query parameters etc.

Moreover, I found DAX formulae for Running Totals but these are straight running total for the whole data set. What if I wish to break/group running totals for each Qualification Programs?

[v-huizhn-msft]

Hi @kamran,

Many fucntions are not used in Direct Query model. I recommand you load you data using import model.

Then you can create measures using the formulas, and get expected result.

New Enrolments = CALCULATE(COUNTA(Test[StudentNumber]),FILTER(Test,Test[time]=1))

Total = CALCULATE(Test[New Enrolments],FILTER(ALL(Test),Test[Index]<=MAX(Test[Index])))

Then create a matrix visual shown the first screenshot, and second visual shown in the following second screenshot.

If you have any question, please let me know.

Best Regards,
Angelia