Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all:
I need to measure the median of releases on a particular application for a certain period of time (based on the filters chosen)
I have a list of software releases done daily .Sample data below
APPLICATION | RELEASE DATE (d/m/y) |
a | 3/1/23 |
b | 4/1/23 |
c | 4/1/23 |
c | 4/1/23 |
d | 8/1/23 |
a | 11/1/23 |
d | 13/1/23 |
a | 13/1/23 |
I summarized number of releases based on Release Date
RELEASE DATE | # OF RELEASES |
3/1/23 | 1 |
4/1/23 | 3 |
8/1/23 | 1 |
11/1/23 | 1 |
13/3/23 | 2 |
Then I was able to create a table considering the entire period of time, adding days with 0 releases. Now I can have the median of all the applications together (With the example below, the median would be 1 because I need to consider the days with 0 releases as well.)
DATE | # OF RELEASES |
1/1/2023 | 0 |
2/1/2023 | 3 |
3/1/2023 | 1 |
4/1/2023 | 3 |
5/1/2023 | 0 |
6/1/2023 | 0 |
7/1/2023 | 2 |
8/1/2023 | 0 |
9/1/2023 | 1 |
10/1/2023 | 0 |
11/1/2023 | 2 |
12/1/2023 | 13 |
13/1/2023 | 5 |
14/1/2023 | 6 |
15/1/2023 | 2 |
16/1/2023 | 1 |
17/1/2023 | 3 |
18/1/2023 | 0 |
19/1/2023 | 1 |
20/1/2023 | 0 |
21/1/2023 | 0 |
22/1/2023 | 0 |
23/1/2023 | 8 |
24/1/2023 | 4 |
25/1/2023 | 5 |
26/1/2023 | 0 |
27/1/2023 | 3 |
28/1/2023 | 4 |
29/1/2023 | 1 |
30/1/2023 | 5 |
31/1/2023 | 0 |
But what I really need to measure is the median of releases of specific applications, countries, teams given a certain period of time (based on the filters). The problem is that, when I use filters, for instance, application, it will show me only days when that application had releases, and median is calculated only with those rows and I need to consider the entire period of time (and the days with 0 releases)
I hope I'm clear enough.
Thanks in advance
W.
Hi @Xiaoxin
I've tried what you suggested and changed formula but it still does not show anything.
Hi @walterdp,
I'd like to suggest you create a calendar table with whole date ranges, then you can use the calendar date as axis to write a measure formula to calculate with raw table records.
For not matched zero value row calculations, you can add variable in measure with summarize function to use current calendar date to look up raw table records. Then you can use iterator function medianx function to aggregate these records.
formula =
VAR summary =
SUMMARIZE (
ALLSELECTED ( 'Calendar' ),
[Date],
"RCount",
CALCULATE (
COUNTA ( 'Table'[APPLICATION] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = [Date] )
)
)
RETURN
MEDIANX ( summary, [RCount] )
Regards,
Xiaoxin Sheng
Hi, @v-shex-msft
Thanks for replying. You'll have to forgive me because I'm quite new in PowerBI, so some things are still unclear for me. Let me see if I understood correctly.
I'd like to suggest you create a calendar table with whole date ranges, then you can use the calendar date as axis to write a measure formula to calculate with raw table records.
Done:
The formula (measure I suppose) was created. I created it in the table that has all the days and the deployments (the days with no deployments have a "0" on that field), but I'm not sure whether that's the place.
medida =
VAR summary =
SUMMARIZE (
ALLSELECTED ( 'Calendario' ),-- calendar table
[Date],
"RCount",
CALCULATE (
COUNTA ( 'Pasos a Prod'[Sistema impactado] ), -- this is the table with the raw data and the column with the application's name
FILTER ( ALLSELECTED ( 'Pasos a Prod' ), 'Pasos a Prod'[Fecha Paso a prod real] = [Date] ) -- same table as before, field is the deployment date
)
)
RETURN
MEDIANX ( summary, [RCount] )
As of now, it does not show anything when I add that formula to a visual object, so I know I'm doing something wrong 🙂
Thanks!
W.
HI @walterdp,
I try to modify the formula and use the max function to extract the current date value from row context instead of directly to use the column. You can try to use the new version if it works:
medida =
VAR summary =
SUMMARIZE (
ALLSELECTED ( 'Calendario' ),
[Date],
"RCount",
CALCULATE (
COUNTA ( 'Pasos a Prod'[Sistema impactado] ),
FILTER (
ALLSELECTED ( 'Pasos a Prod' ),
'Pasos a Prod'[Fecha Paso a prod real] = MAX ( 'Calendario'[Date] )
)
)
)
RETURN
MEDIANX ( summary, [RCount] )
If these also not helped, can you please share a pbix with some dummy data to test?(you can upload to network diver and share link here, please not attach any sensitive data in it) We can coding formula and test on it.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Sorry for the delay but I was involved in another issue with Power BI.
Here's the link to the file
Delving into the issue I believe we need to create a table with three columns, "Date", "Application", and "Number of deployments". This table would take information from the tables "Pasos a Produccion" and "Calendario", filling the days with 0 the days with no deployments for EACH application. Something like the example below (in the example we consider only 5 applications as the whole universe)
DATE | APLICATION | NUMBER OF DEPLOYMENTS |
1/1/23 | X-11 | 0 |
1/1/23 | Y-12 | 0 |
1/1/23 | Z-13 | 0 |
1/1/23 | E-15 | 0 |
1/1/23 | G-13 | 0 |
2/1/23 | X-11 | 0 |
2/1/23 | Y-12 | 0 |
2/1/23 | Z-13 | 0 |
2/1/23 | E-15 | 0 |
2/1/23 | G-13 | 0 |
3/1/23 | X-11 | 0 |
3/1/23 | Y-12 | 0 |
3/1/23 | Z-13 | 2 |
3/1/23 | E-15 | 1 |
3/1/23 | G-13 | 0 |
4/1/23 | X-11 | 1 |
4/1/23 | Y-12 | 1 |
4/1/23 | Z-13 | 2 |
4/1/23 | E-15 | 0 |
4/1/23 | G-13 | 0 |
5/1/23 | X-11 | 0 |
5/1/23 | Y-12 | 1 |
5/1/23 | Z-13 | 1 |
5/1/23 | E-15 | 0 |
5/1/23 | G-13 | 1 |
… | … | … |
31/12/23 | X-11 | 3 |
31/12/23 | Y-12 | 0 |
31/12/23 | Z-13 | 0 |
31/12/23 | E-15 | 1 |
31/12/23 | G-13 | 2 |
Hi @walterdp,
I modify the formulas and the current it seems work on your sample pbix file, and it can also be responds with the filter effects. You can try to use the following measure formula if it works on your side:
medida =
VAR summary =
SUMMARIZE (
ALLSELECTED ( 'Calendario' ),
[Date],
"RCount",
CALCULATE (
COUNTA ( 'Despliegues por Día (v)'[Sistema impactado] ),
FILTER (
ALLSELECTED ( 'Despliegues por Día (v)'),
[Fecha Paso a prod real] = [Date]
)
)
)
RETURN
MEDIANX ( summary, [RCount] )
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Thanks for the reply. I've tried your solution but it did not work as expected. Maybe it is because I'm not understanding how to use it or maybe I'm not clear about what we need.
If it is the first one, I've added your formula to the file and now you can see the results in two different sheets.
In case I did not explain myself, I added an Excel sheet with an example of what I think would be a solution (somehow).
The table at the left shows an extract of what is in the PowerBI data
There, the calculations do not work as the table does not contain the days with 0 deploys.
The second table is a manual creation, based on the first one, including all the days in 2022. THere, the calculations are the ones I need, but adding filters such as application, period of time, etc.
Thanks
W.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |