Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I'm working with data from a ticketing tool. For each location, I have a some tickets and for each ticket, a number of service unavailabity.
For each location, I need to calculate the rate of availabity with this formulas :
(Number of hours in the month - Number of Hours on unavailability) / Number of hours in the month
For example in janv-21 : 31 days => 31*24 = 744 hours
Rate of availability => (744 - 257.81)/ 744 = 65.35 %
For information, in the DimDate, I add the numbers of hours in each month
And finally for all the year, the rate of availabilty is 44,27 %
But It is wrong because in february/may/june& november, I haven't ticket in the fact table, so the rate of availability for these 4 months must be 100 % (AND NOT BLANK...) . So my global KPIs (for one year) and for the "Location1" must be :
For information? I need to display the global "RateOfAvailability" for all location (average of rate of availability per location) and the repartition per month. Also I have a lot of slicer, so we can not use a table with DAX.
I try with SUMMARIZE, but as I don't have rows for some couples "Location/Month" in the fact table, the measure is false :
Thank you Amitchandak, but I don't understand what you want to calculate.
My query work for the month with data in fact table (I upddated the query with good name 🙂 😞
Hi, @VGuichard
I didn't understand your question, do you want to show 100% in the bar chart? And is the value in the card visual correct? What's the difference between your current result and your desired result?
Janey
@VGuichard , Try a measure like
available Hours
sumx(values('Date'[Month Year]),day(max(fact[Date]))*24 )
or
sumx(values('Date'[Month Year]),day(eomonth(max(fact[Date]),0) )*24 )
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |