cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wolfjnh Frequent Visitor
Frequent Visitor

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

modell.PNG

 

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.

 

simple example.PNG

 

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. 

 

 

 

 

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Creating average on month with blanks

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.

View solution in original post

wolfjnh Frequent Visitor
Frequent Visitor

Re: Creating average on month with blanks

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

View solution in original post

4 REPLIES 4
Stachu Super Contributor
Super Contributor

Re: Creating average on month with blanks

can you share an anonymised sample of your data?
you can follow the advice here
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Creating average on month with blanks

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.

View solution in original post

wolfjnh Frequent Visitor
Frequent Visitor

Re: Creating average on month with blanks

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

View solution in original post

Super User
Super User

Re: Creating average on month with blanks

@wolfjnh

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

Best

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 342 members 3,297 guests
Please welcome our newest community members: