locked
Power BI: Average of a DistinctCount RRS feed

  • Question

  • I have the following tables:

    FactAssign { FactKey, BranchID, ClientID, CustomerName, StartDate, CalendarWeekKey, EmployeeguId }

    DimBranch { BranchID, BranchName, Region}

    DimClient { clientID, ClientName }

    DimCalendar { CalendarWeekKey, WeekEndingDate, CalendarYear, CalendarWeek }

    Data from FactAssign table here

    Result I needenter image description here

    Here <g class="gr_ gr_28 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="28" id="28">CurrentWeek</g> 50 is the Average of the distinct count of Employees per day for branchID 4 for this week. Distinct Counts of Employees this week are 56,53,48,47,46 respectively from Monday thru Friday.

    How can I get the AVERAGE of the DISTINCTCOUNT of Employees per branch per Week?

    Dax I used :

    Averagex = CALCULATE(AVERAGEX( VALUES(TestingAverageX[CalendarWeekKey]), DISTINCTCOUNT(TestingAverageX[EmployeeGUID])), FILTER(TestingAverageX, TestingAverageX[CalendarWeekKey] = 20190303))

    Regards, Success

    imgur.com/kYnrmuN 

    Regards,
    Success


    • Edited by Success t0rres Sunday, March 3, 2019 7:13 AM updates question with more details
    Friday, March 1, 2019 10:44 AM

Answers

  • Ah, sorry, you'll need to wrap the distinct count in an calculate to force the calculation of the daily distinct count (otherwise it's just calculating the weekly distinct count 7 times) 

    Averagex = AVERAGEX( 
       VALUES(TestingAverageX[StartDate])
       , CALCULATE( DISTINCTCOUNT(TestingAverageX[EmployeeGUID]) )
    )


    http://darren.gosbell.com - please mark correct answers

    Sunday, March 3, 2019 7:46 AM

All replies

  • The problem is that you are doing an averagex at the week level and it looks like you actually want to average the daily counts. So swapping the column that you are iterating over should fix this.

    eg.

    Averagex = AVERAGEX(
       VALUES(TestingAverageX[StartDate])
       , DISTINCTCOUNT(TestingAverageX[EmployeeGUID])
    )


    http://darren.gosbell.com - please mark correct answers

    Friday, March 1, 2019 10:07 PM
  • Hey Darren,

    thanks for your reply. I had tried this before as well. IT doesn't change the output when I put the weekkey in the visual as I need a weekly average of the distinct count of Employees per day per branch. 

    Please see image here.

    Regards,

    Success

    Sunday, March 3, 2019 6:55 AM
  • Ah, sorry, you'll need to wrap the distinct count in an calculate to force the calculation of the daily distinct count (otherwise it's just calculating the weekly distinct count 7 times) 

    Averagex = AVERAGEX( 
       VALUES(TestingAverageX[StartDate])
       , CALCULATE( DISTINCTCOUNT(TestingAverageX[EmployeeGUID]) )
    )


    http://darren.gosbell.com - please mark correct answers

    Sunday, March 3, 2019 7:46 AM
  • Hey Darren,

    Thank you very much. You made my day. Have a good day yourself. I have marked your post as the answer. :)

    Sunday, March 3, 2019 11:15 AM