Forum Discussion

chein's avatar
chein
Frequent Visitor
7 years ago

Average of Measure Total when counting active projects

Hello everybody,

 

this is my first post so I´m sorry if I make some mistakes posting my Problem.

 

 

In my Power BI file I have two tables: "Consultants" with the name of a consultant, a start- and an end-date and "calender" with dates form January 1st 2016 till December 31st 2020.

I have a measure that counts "active" consultants which is defined like this: 

Active Consultants = CALCULATE (COUNTROWS ( Consultants );FILTER (Consultants;(Consultants[Start] <= LASTDATE ( calender[Date] )&& Consultants[End] >= FIRSTDATE ( calender[Date] ))))
 
My second measure just counts the days of the calender:
Days = COUNTROWS(calender)
And the third one multiplies my two other measures:
Days x Active Consultants = [Days]*[Active Consultants]
 
What I´m trying to achieve is to get the total of a year for "Days x Active Consultants" as the sum of the line/different months. I know, that the logic behind the measure doesn´t allow the total to be the total of the line. But I couldn´t find a solution to my problem in this forum. I found a lot of threads where people fixed similar problems with SUMMARIZE, but I don´t think this is working in my case. One approach could be to get the average of the "Active Consultants" as total of the year, but again, I have no clue how to to it and I think SUMMARIZE won´t help.
 
Does anybody have an idea on how to solve this problem.
 
Thanks in advance
Chris
 
Need Help

2 Replies

  • hthota's avatar
    hthota
    Icon for Resolver III rankResolver III
    7 years ago

    Do you need the total duration of 3rd measures in Years'

     

    Is I am correct. if not please correct me,

     

    Thanks,

    Hemanth Thota.

    • chein's avatar
      chein
      Frequent Visitor
      7 years ago

      Hi hthota ,

       

      No, thats not what i want. I want the red outlined value to be the total of the line "Days x Active Consultants" (so in fact 11377 + 10640 + 12028 ... = 108169). But I don´t think that I can adjust this measure to calculate the total right. So the first step to get into the right direction in my eyes would be to get the green outlined value to be the average of the line "Active Consultants".

       

      Regards,

      Chris