Forum Discussion
Helper ICalculate median based on dynamic information
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
walterdpHi, Anonymous
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
- walterdp
Hi Anonymous
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