Summarize without rows in fact table

Question

Hello,&nbsp;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.&nbsp;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) /&nbsp;Number of hours in the monthFor example in janv-21 : 31 days =&gt; 31*24 = 744 hours&nbsp;Rate of availability =&gt; (744 - 257.81)/ 744 = 65.35 %&nbsp;&nbsp;For information, in the DimDate, I add the numbers of hours in each monthAnd finally for all the year, the rate of availabilty is 44,27 %&nbsp;But It is wrong because in february/may/june&amp; november, I haven't ticket in the fact table, so the rate of availability for these 4 months must be 100 %&nbsp; (AND NOT BLANK...) . So my global KPIs (for one year) and for the "Location1" must be :&nbsp;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.&nbsp;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% = &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(Fact_ServiceRequest&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,Dim_ServiceTicket[X_NOM_COURT_ADRESSE]&nbsp; &nbsp; &nbsp; &nbsp; --Location&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,Dim_Date[MoisAnnéeInt]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,"RateOfAvailability%",IF(countrows(F_Incident)=0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,DIVIDE(MAX(D_Date[NbJourMois])*24-SUM(F_Incident[X_TPS_INDISPO]),MAX(D_Date[NbJourMois])*24)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,[TpsIndispo%]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp;Thanks you for your help&nbsp;&nbsp;&nbsp;

amitchandak · Answer

VGuichard ,&nbsp; Try a measure like
available Hours&nbsp;
sumx(values('Date'[Month Year]),day(max(fact[Date]))*24 )
&nbsp;
or
&nbsp;
sumx(values('Date'[Month Year]),day(eomonth(max(fact[Date]),0) )*24 )

vguichard · Answer

Thank you Amitchandak, but I don't understand what you want to calculate.&nbsp;My query work for the month with data in fact table (I upddated the query with good name 🙂 😞&nbsp;&nbsp;RateOfAvailability% =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(Fact_ServiceRequest&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,Dim_ServiceTicket[X_NOM_COURT_ADRESSE]&nbsp; &nbsp; &nbsp; &nbsp; --Location&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,Dim_Date[MoisAnnéeInt]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,"RateOfAvailability%",IF(countrows(Fact_ServiceRequest)=0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,DIVIDE(MAX(D_Date[NbDaysByMonth])*24-SUM(Fact_ServiceRequest[Nb_Of_Hours_Unavailable]),MAX(D_Date[NbDaysByMonth])*24)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,[RateOfUnavailability%]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp;The question is how to date the month without Ticket (100 % of avaibility by defaut)&nbsp;Thanks

v-janeyg-msft · Answer

Hi,&nbsp;VGuichard&nbsp;
&nbsp;
I didn't understand your question, do you want to show 100% in the bar chart? And&nbsp;is the value in the card visual correct?&nbsp;What's the difference between your current result and your desired result?

&nbsp;
Janey
&nbsp;

v-janeyg-msft · Answer

VGuichard&nbsp; Any updates?

Forum Discussion

Summarize without rows in fact table

4 Replies

Related content

Merge Summarize Table

Parameter table dimensions - summarize

SUMMARIZE Table

Parameter in summarize

Summarize average monthly table