The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
my SQL table looks like this:
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.
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.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
81 | |
62 | |
54 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |