Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Janne_Lappi
Frequent Visitor

Percentile of the latest measurement values of assets

Hello,

 

my SQL table looks like this:

gas_analysis.JPG

etc......

 

The oil in the assets in sampled from time to time and the amounts of certain gases formed in the oil are measured.

 

I want to calculate the 90 % percentile of the latest CO values (ppm), meaning the value below which 90% of my assets fall regarding their current CO value. I also would like to have a date slicer so that I could see what the current percentile value was at a date in the past.

 
The latest sample dates I can have with a measure:
latest_date = CALCULATE(LASTNONBLANK(Table[sample_date];1))
 
And the latest CO values with this measure:
CO_ppm_latest = var latest = [latest_date] RETURN CALCULATE(MAX(Table[ppm]);FILTER(Table; Table[sample_date] = latest ); Table[gas] = "CO")
 
How to calculate the 90% percentile? The PERCENTILE.INC function does not allow measures as input so I cannot use CO_latest.
 
I have tried many different approaches but none of them works. I created a calculated table "Table2" that includes the latest CO value of each asset because percentile function requires a base table column as input.
 
Table_CO = SUMMARIZE( FILTER(Table; Table[gas] = "CO");Table[asset_key]; "latest_date_CO"; Table[latest_date];"CO";Table[CO_ppm_latest])
 
Then a measure:
90percentile = PERCENTILE.INC(Table_CO[CO];0,9)
 
This returns the correct value when there is no time filter. But when I move the sample_date slicer it returns a wrong value. The problem is that the slicer does not work for the latest_date_CO column of the calculated table Table_CO.
 
I will appreciate any help. Thank you.
 
Best regards,
Janne
4 REPLIES 4
savvari
Helper I
Helper I

@Janne_Lappi 

 

Do you expect to have multiple values for CO ppm on latest date? And need to find 90th percentile of these values?

 

If so, your measure CO_ppm_latest only returns a single value. And hence you cannot pass it to PERCENTILE.INC

 

Your second approach seems more appropriate.

 

The date slicer is on which date field? The same Sample date (of which you calculated the latest date)? Or a different a date field?

 

Remember that in the calculated table, you already limited the CO ppm values only to the latest sample date. It doesn't have any other date value.

 

If you are looking at a different date field, include it in the calculated table.

 

Let me know if I got it completely wrong. If possible, share your pbix so that I can try the measure.

 

Thanks,

savvari

 

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

Hello,

 

thanks for the reply. I am sorry, I cannot share the data.

 

Each asset has a number of sample_dates as the oil inside the assets is sampled once in a year or so. One sample includes one value for Carbon Monoxide. I want to calculate the 90 % percentile of the latest CO values. CO is a measure of asset condition so the percentile is a measure of the condition of the entire asset fleet. So, if I pick the latest sample date for each asset and consider only gas = CO, the table would look like below. And I want to calculate the percentile of these ppm values.

 

gas_analysis_excel.JPG

 

Measure "CO_ppm_latest" displays this latest CO value for each asset when it is in a table visual with "asset_key", measure "latest_date" and "ppm". I don't know if I can make use of "CO_ppm_latest" in the percentile calculation.

 

I have a slicer for "sample_date" which is the original date field in the source table. By limiting the end date I can go back in time and see what the situation with CO was at a timestamp in the past as the "latest_date" measure is based on "sample_date". The problem is that the "sample_date" slicer does not work for the calculated table in a way I thought. The "CO" column in the calculated table still includes the latest CO value ignoring the slicer, and the latest_date_CO column also has the latest dates ignoring the slicer.

 

So the percentile calculated out of the column "CO" of the calculated table works for today, when no date filter is applied with the slicer. But it does not work when applying the filter with the slicer as the calculated table is not following the slicer in a way I would like.

 

Best regards,

Janne

Hi @Janne_Lappi ,


How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Janne_Lappi ,

 

we can try to create measure use following formula to meet your requirement:

 

CO =
VAR t =
    SUMMARIZECOLUMNS (
        'Table'[asset_key],
        "ppm_Last",
        VAR last_date =
            CALCULATE ( MIN ( 'Table'[latest_date] ) )
        RETURN
            CALCULATE (
                SUM ( 'Table'[ppm] ),
                FILTER ( 'Table', 'Table'[gas] = "CO" && 'Table'[latest_date] = last_date )
            )
    )
VAR t2 =
    ADDCOLUMNS ( t, "index", RANKX ( t, SUM ( [ppm_Last] ),, ASC ) )
VAR maxnumber =
    COUNTROWS ( t2 )
VAR index90 =
    ROUND ( maxnumber * 0.9, 0 )
RETURN
    MAXX ( FILTER ( t2, [index] = index90 ), [latest_date] )

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.