Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 = 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.
Solved! Go to Solution.
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.
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |