Summarize without rows in fact table

[VGuichard]

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 :

RateOfAvailability% =

            AVERAGEX(

                          SUMMARIZE(Fact_ServiceRequest

                         ,Dim_ServiceTicket[X_NOM_COURT_ADRESSE]        --Location

                         ,Dim_Date[MoisAnnéeInt]

                        ,"RateOfAvailability%",IF(countrows(F_Incident)=0

                             ,1

                             ,DIVIDE(MAX(D_Date[NbJourMois])*24-SUM(F_Incident[X_TPS_INDISPO]),MAX(D_Date[NbJourMois])*24)

                          )

           ,[TpsIndispo%]

           )

Thanks you for your help

[v-janeyg-msft]

@VGuichard  Any updates?

[VGuichard]

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 🙂 😞 

RateOfAvailability% =

            AVERAGEX(

                          SUMMARIZE(Fact_ServiceRequest

                         ,Dim_ServiceTicket[X_NOM_COURT_ADRESSE]        --Location

                         ,Dim_Date[MoisAnnéeInt]

                        ,"RateOfAvailability%",IF(countrows(Fact_ServiceRequest)=0

                             ,1

                             ,DIVIDE(MAX(D_Date[NbDaysByMonth])*24-SUM(Fact_ServiceRequest[Nb_Of_Hours_Unavailable]),MAX(D_Date[NbDaysByMonth])*24)

                          )

           ,[RateOfUnavailability%]

           )

The question is how to date the month without Ticket (100 % of avaibility by defaut)

Thanks

[v-janeyg-msft]

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

[amitchandak]

@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 )