Forum Discussion

walterdp's avatar
walterdp
Icon for Helper I rankHelper I
3 years ago

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 be...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

walterdp's avatar
walterdp
Icon for Helper I rankHelper I
3 years ago

Hi Anonymous 

 

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
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • walterdp's avatar
      walterdp
      Icon for Helper I rankHelper I
      3 years ago

      Hi Anonymous 

      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

       

       

      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.