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
walterdp
Helper I
Helper I

Calculate median based on dynamic information

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

 

APPLICATIONRELEASE DATE (d/m/y)
a3/1/23
b4/1/23
c4/1/23
c4/1/23
d8/1/23
a11/1/23
d13/1/23
a13/1/23

 

I summarized number of releases based on Release Date

 

 

RELEASE DATE# OF RELEASES
3/1/231
4/1/233
8/1/231
11/1/231
13/3/232

 

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/20230
2/1/20233
3/1/20231
4/1/20233
5/1/20230
6/1/20230
7/1/20232
8/1/20230
9/1/20231
10/1/20230
11/1/20232
12/1/202313
13/1/20235
14/1/20236
15/1/20232
16/1/20231
17/1/20233
18/1/20230
19/1/20231
20/1/20230
21/1/20230
22/1/20230
23/1/20238
24/1/20234
25/1/20235
26/1/20230
27/1/20233
28/1/20234
29/1/20231
30/1/20235
31/1/20230

 

 

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.

7 REPLIES 7
walterdp
Helper I
Helper I

Hi @Xiaoxin

 

I've tried what you suggested and changed formula but it still does not show anything.

 

walterdp_0-1674044430088.png

 

walterdp_1-1674044633024.png

 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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: 

walterdp_0-1673354187455.png

walterdp_1-1673354210634.png

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

PowerBI

 

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)

 

 

DATEAPLICATIONNUMBER OF DEPLOYMENTS
1/1/23X-110
1/1/23Y-120
1/1/23Z-130
1/1/23E-150
1/1/23G-130
2/1/23X-110
2/1/23Y-120
2/1/23Z-130
2/1/23E-150
2/1/23G-130
3/1/23X-110
3/1/23Y-120
3/1/23Z-132
3/1/23E-151
3/1/23G-130
4/1/23X-111
4/1/23Y-121
4/1/23Z-132
4/1/23E-150
4/1/23G-130
5/1/23X-110
5/1/23Y-121
5/1/23Z-131
5/1/23E-150
5/1/23G-131
31/12/23X-113
31/12/23Y-120
31/12/23Z-130
31/12/23E-151
31/12/23G-132

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] )

1.PNGRegards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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).

Example.xlsx

 

The table at the left shows an extract of what is in the PowerBI data

 

walterdp_2-1675194865056.png

 

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.

 

walterdp_1-1675194844889.png

 

Thanks

W.

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.