Average of measure involving two tables

[Philippe_Jamon]

Hi everybody,

I have a problem,

I use a measure in my data model that use two tables.

I have a table named HoursWorked that contains sum of working hours in each month.

The other table is IncidentReport. It contains incident with DateStart of loss time and DateEnd for each incident.

I made a measure to calculate the number of loss days for each month for each incident

NumberOfDaysByMonth =
CALCULATE(
    COUNTX(
        SUMMARIZE(
            FILTER(
                CROSSJOIN('IncidentReport','Dates TF TG'),
                'Dates TF TG'[Date] >= 'IncidentReport'[DateStartLossTime]
                    && 'Dates TF TG'[Date] <= 'IncidentReport'[DateEndLossTime]
            ),
'IncidentReport'[FirstName],'Dates TF TG'[Date]
        ),
       'Dates TF TG'[Date]
    )
)

I made another measure to calculate the incident rate.

IncidentRate = if(ISBLANK(SUM('HoursWorked'[NbHeures]))&&not(ISBLANK([NumberOfDaysByMonth])),"",
[NumberOfDaysByMonth] / SUM('HoursWorked'[NbHours]) *1000)

I would like to create a measure to calculate the rolling average of IncidentRate but I don't know how to do that.

Here's a sample of my dashboard.

Thanks for your help.

Have a nice day.

[v-xinruzhu-msft]

Hi,

Thanks for the solutions @Greg_Deckler  and @Ashish_Mathur  provided, i want to offer some more information for user to refer to.

hello @Philippe_Jamon , based on your description, I create the following measures to display the rate and rolloing rate. and in the matrix, i replace  your [Month & Year] field to [MonthnYear] field.

Rate =
VAR a =
    ADDCOLUMNS (
        CROSSJOIN (
            SUMMARIZE ( ALLSELECTED ( Incident ), [Name] ),
            SUMMARIZE ( ALLSELECTED ( Dates ), [MonthnYear] )
        ),
        "Numbers_days", [Number of days by month]
    )
VAR b =
    SUMX ( FILTER ( a, [MonthnYear] <= MAX ( Dates[MonthnYear] ) ), [Numbers_days] )
VAR c =
    SUMX (
        FILTER (
            ALLSELECTED ( HoursWorked ),
            INT ( FORMAT ( [Month], "YYYYMM" ) ) <= MAX ( Dates[MonthnYear] )
        ),
        [NBHours]
    )
RETURN
    DIVIDE ( b, c ) * 1000

Rolling Rate =
VAR a =
    SUMX (
        FILTER (
            ALLSELECTED ( Dates[MonthnYear] ),
            [MonthnYear] <= MAX ( Dates[MonthnYear] )
        ),
        [Rate]
    )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( Dates[MonthnYear] ),
        ALLSELECTED ( Dates[MonthnYear] ),
        Dates[MonthnYear] <= MAX ( Dates[MonthnYear] )
    )
RETURN
    DIVIDE ( a, b )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Philippe_Jamon]

Thanks @v-xinruzhu-msft for your answer,

I think we are getting closer to the good answer but I think it's not working perfectly for the moment.

As you can see, my Incident rate hasn't the same value as your Rate measure (and i checked the results, the good ones are the ones from my measure).

And also, in your measure, you have data for october november and december, even if there's no number of day by month for this month.

Thanks again for your help.

Philippe.

[v-xinruzhu-msft]

Hi @Philippe_Jamon 

Thanks for your quick reply, but based on your description before, you want to calculate like the following.

(Nb of days by month Jan + Feb / Nb of hours Jan + Feb) * 1000

--> (22+23) / (17716.1+13822.9) * 1000 = 1.426.

 but your measure is not 1.426, or can you provide some more logic about the logic about the calculation ?

 for the incident rate, for the March, how do you calculate it?

Best Regards!

Yolo Zhu

[Philippe_Jamon]

Hi @v-xinruzhu-msft ,

Thanks for you answer.

You're right for the rolling incident rate but not for the normal incident rate.

The normal incident rate is like the following :

(Nb of days by month / Nb of hours ) * 1000 so for example for March

--> 22/15092.8 * 1000 = 1.46

You can see that my Incident rate is 1.46 but yours is 1.44.

Thanks again for your help.

Have a nice day.

Philippe.

[v-xinruzhu-msft · Accepted solution]

Hi @Philippe_Jamon 

Thanks for your reply, based on your description, I change  the measure to the following.

Rolling_Rate =
VAR a =
    ADDCOLUMNS (
        CROSSJOIN (
            SUMMARIZE ( ALLSELECTED ( Incident ), [Name] ),
            SUMMARIZE ( ALLSELECTED ( Dates ), [MonthnYear] )
        ),
        "Numbers_days", [Number of days by month]
    )
VAR b =
    SUMX ( FILTER ( a, [MonthnYear] <= MAX ( Dates[MonthnYear] ) ), [Numbers_days] )
VAR c =
    SUMX (
        FILTER (
            ALLSELECTED ( HoursWorked ),
            INT ( FORMAT ( [Month], "YYYYMM" ) ) <= MAX ( Dates[MonthnYear] )
        ),
        [NBHours]
    )
RETURN
    IF ( [Incident Rate] <> BLANK (), DIVIDE ( b, c ) * 1000 )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Philippe_Jamon]

@v-xinruzhu-msft you're a magician !!

Thanks it works fine.

I have a last question.

Do you think it's possible to have Month & year in column instead of Monthnyear (visually i prefer to have it Like Jan. 2024).

Thanks again.

Philippe.

[Philippe_Jamon]

@v-xinruzhu-msft  Thanks again for your help.

Do you think it's possible to have Month & year in column instead of Monthnyear (visually i prefer to have it Like Jan. 2024).

Thanks again.

Philippe.

[Ashish_Mathur]

Hi,

This is not the best way but there is no other way i am aware of.  We can expand the Accidents table to show one row for each date which falls between the start and end date.  So ID 3 will be split into multiple rows where each row would be a day between the start and end date.  While this will give rise to a large table, it will certainly solve your question.

[Philippe_Jamon]

Hi @Ashish_Mathur ,

Thanks for your answer.

I don't really understand why splitting each incident in multiple row could help me.

The measure calculating the lost time days by month works fine. And the second measure calculating the incident rate also works fine.

What I want is to be abble to have a running average of the incident rate in order to show it in a line chart for example.

Thanks and have a nice day.

Philippe.

[Greg_Deckler]

@Philippe_Jamon This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Also, see this: Better Rolling Average - Microsoft Fabric Community

[Philippe_Jamon]

@Greg_Deckler , Thanks for your answer.

I don't really understand the solution you gave me.

Because, the measure I want to average is calculated with data from two different tables and so i don't understand how i can use your formula.

Thanks again.

Philippe.

[Greg_Deckler]

@Philippe_Jamon I don't see the Incident Rate in your sample file and not sure how you are intending to use it exactly. You can use SUMMARIZE with data from multiple tables assuming you have a relationship between those tables like a dimension related to a fact table. Would need to better understand what you are trying to do exactly. But, for example, if you wanted the average of your incident rate over months, you would do this:

Measure = AVERAGEX( SUMMARIZE( 'Dates', [Month & Year], "__Value", [IncidentRate] ), [__Value] )

Now, if you wanted a 3 month rolling average it might go something like this:

Better Rolling Average = 
    VAR __EndDate = MAX('Dates'[Date])
    VAR __3MonthsAgo = EOMONTH(__EndDate, -3)
    VAR __StartYearMonth = YEAR(__3MonthsAgo) * 100 + MONTH(__3MonthsAgo)
    VAR __EndYearMonth = YEAR(__EndDate) * 100 + MONTH(__EndDate)
    VAR __Table = 
        SUMMARIZE(
            FILTER(ALL('Dates'),[MonthnYear]>=__StartDate && [MonthnYear]<=__EndDate),
            [Month],
            "__Value",[IncidentRate]
        )
RETURN
    AVERAGEX(__Table,[__Value])

[Philippe_Jamon]

@Greg_Deckler , I tried your solution but it gave me exactly the same value as the IncidentRate measure. (sorry but in the sample file, the IncidentRate measure is named TG)

Below a screenshot of the measure from your formula.

What i would like is to have a measure that works like that :

Thanks again.

Philippe.

[Greg_Deckler]

@Philippe_Jamon I think we need to back up on this one. I noticed in your semantic model that none of your tables are related to one another. That seems off and not normal. Perhaps you could give me a better idea of what exactly you are trying to accomplish. Your Number of days by month measure seems quite overcomplicated. Also, because you don't have any relationships, your MAX('HeuresTravaillees'[NBHeures]) in your TG measure is always going to return 17716.1 for January 2023 which I can't imagine is what is desired.

[Philippe_Jamon]

@Greg_Deckler , thanks for helping me.

There was indeed a problem with my sample date.

Here’s a new one with good connections.

For the Number of days by month measure, that’s the only way I found to show the number of days between start date and end date distributed by month for each incident.

(If I make a connection between table date and incident date, it won’t distribute by month the days loss but only show the total).

The Number of days by month measure works fine.

The Incident rate measure works fine by month and also in total.

I put your formula in the Running Incident rate measure and it shows the same values as the normal Incident rate.

What I would like for the Running Incident Rate (for February for example) is the following :

(Nb of days by month Jan + Feb / Nb of hours Jan + Feb) * 1000

--> (22+23) / (17716.1+13822.9) * 1000 = 1.426.

I hope you’ll be able to help me.

Thanks again.

Philippe.

[Philippe_Jamon]

@Greg_Deckler , the date table has no relation with the "Accident du travail" table because it's the only way to be abble to show the number of incident loss by month.

The number of days by month is used to show the number of loss day by month between two dates (start date and end date).

This measure is working fine. But i use the measure to make an incident rate (TG). And it's the TG measure that I want to have as a rolling average.

Thansk again for your help.

[Greg_Deckler]

@Philippe_Jamon Did you reade this? "Also, because you don't have any relationships, your MAX('HeuresTravaillees'[NBHeures]) in your TG measure is always going to return 17716.1 for January 2023 which I can't imagine is what is desired." Is that what is desired or should TG be using the appropriate month's value?