Forum Discussion

kamran's avatar
kamran
Frequent Visitor
9 years ago

Need conditional Count/Running Total from a Data Set

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...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
    kamran
    Frequent Visitor
    9 years ago

    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's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      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