Forum Discussion

wolfjnh's avatar
wolfjnh
Frequent Visitor
7 years ago
Solved

Creating average on month with blanks

Hi,

 

I have a small problem, which lifts me to my edge.

 

I have two tables:

 

Dates - typical date dimension with one row per day

SOC Tickets - tickets from our bug tracking tool with different categories etc.

 

Connected on Datum and Erstellt - Datum

 

Now I just want to create the average amount of tickets per month.

I started with:

= COUNT('SOC Tickets'[key]) / DISTINCTCOUNT('SOC Tickets'[Monat mit Jahr]);

Monat mit Jahr is Yearmonth and is just a related DAX: 
Monat mit Jahr = RELATED(Dates[Monat mit Jahr])

 

So far, so easy. I thougt, this would work, but when I filter on the data, I had to find out, that months without data for this category are ignored in the calculation. I added a simple example for you.

 

 

My expecatiation is, that PBI takes the month from 01.06.2017 to 31.10.2018 (17) and does following calculation

2 / 17 = 0,1176

but it ignores all months without a value and so I get a

2 / 2 = 1

How can I achive, that PBI gives me the right value? I found similiar posts, but nothing worked for me. 

 

 

 

 

 

 

 

 

 

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi wolfjnh

    Try

    DISTINCTCOUNT('Date'[Monat mit Jahr])

    in your formula

    'SOC tickets'[Monat mit Jahr] only has the months in which there is data.  The 'Date' table has all of them.

  • wolfjnh's avatar
    wolfjnh
    7 years ago

    Hi AlB,

     

    thank you for your post. I think, I found the solution.

     

    First of all, you are right. I have to use the Date-Dimension in this formula.

    But I have also to consider two more things. The connection between the two tables has to be unidirectional and the date filter in my report needs to come from my date table as well.

     

    I will look, if there are side effects to my other reports, but I think, that this is the solution. Simple, just as I thought :)

     

    Regards,

    wolfjnh

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi wolfjnh

    Try

    DISTINCTCOUNT('Date'[Monat mit Jahr])

    in your formula

    'SOC tickets'[Monat mit Jahr] only has the months in which there is data.  The 'Date' table has all of them.

    • wolfjnh's avatar
      wolfjnh
      Frequent Visitor
      7 years ago

      Hi AlB,

       

      thank you for your post. I think, I found the solution.

       

      First of all, you are right. I have to use the Date-Dimension in this formula.

      But I have also to consider two more things. The connection between the two tables has to be unidirectional and the date filter in my report needs to come from my date table as well.

       

      I will look, if there are side effects to my other reports, but I think, that this is the solution. Simple, just as I thought :)

       

      Regards,

      wolfjnh

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        wolfjnh

        Correct on both.  I had not seen you had a bidirectional relationship

        Best