DAX median in pivot table for an "almost empty" column

[Katchi]

Hello everyone,

I feel like this one requires a lot of context. hopefully you won't be lost.

I’m currently working on a spreadhseet supposed to help me monitor power/energy consumption (with a 10min to 5min step delay). To monitor some statistics about the large amount of data this implies (see data spreadsheet), I would like to create pivot tables with their linked charts (see statistics spreadsheet). So far so good.

My problem is two-fold :

• Pivot tables don’t allow me to sum power consumption per day (or at least I don’t know how to do so), so i have to come up with a formula that sums every consumption in one day. And I need to make sure this new column only contains each sum once (hence the remaining cells being blanked out). For information purpose, I do that with an IF function that returns the sum when I reach the right cell I want, and «» (a blank text) if not.
• Secondly, I discovered one can add new measures to a pivot table which is wonderful, and I would definitely like to know the median (and other percentiles) of my new « daily consumption » column (see « daily »). But, I can’t seem to use the DAX median or medianx functions properly for this column. This is where I need help.

As I know my previous choice to make a daily consumption column may be what is blocking me, I encourage anyone who has a better/more clever/ elegant way of doing so, to suggest it as I will most definitely learn from it.

Note : I am French but am very comfortable with English. I remain humble in the ways of excel and am self-taught. Also, the data here are random numbers so nothing to analyse for the moment.


I would like to add a median or medianx measure for my daily consumption column (in the pivot table in the "Statistics" spreadsheet).
Expressions I've tried so far are:

=MEDIAN(FILTER(Plage;ISNUMBER(VALUE(Plage[Daily kWh]))= TRUE())) This one works if I adapt it and type it in a cell on my "Data" spreadsheet.
And the error is :
Cette formule est incomplète ou n’est pas valide : « La fonction MEDIAN accepte uniquement une référence de colonne comme argument numéro 1. ». which means. « This formula is incomplete or not valid. The MEDIAN function only accepts a column reference for reference 1 »

AND

=MEDIANX(FILTER(Plage;ISNUMBER(Plage[Daily kWh])= TRUE()); Plage[Daily kWh])
And the error is :
Cette formule est incomplète ou n’est pas valide : « Erreur de calcul dans la mesure 'Plage'[filtre mediane conso jour] : MEDIANX ne prend pas en charge les expressions de type chaîne/booléen/date. ». which means « This formula is incomplete or invalid. Calculus error in measure « Plage[filtre médiane conso jour]. MEDIANX doesn’t process string/bool/date expressions.

Here is the file on wetransfer : EXCEL DAX MEDIAN PB

Finally, here are a few links that were useful in building my formulae :
Pivot Table Median - Excel Tips - MrExcel Publishing
The surprising middle ground: finding the Median value in Power BI DAX | by Hila Galapo | Medium
MEDIANX, fonction (DAX) - DAX | Microsoft Learn

Thanks for helping or bringing any suggestion.

 

[Katchi]

Hello everyone,

So I gave it some more hours and it happens I have found a fix for my issue.
I first watched this webinar from Mr Excel : Pivot Table Median - Excel Tips - MrExcel Publishing, then these two short videos from Mr Excel once again, to familiarise myself some more with DAX: Maîtrise de la médiane Excel : médiane dans le tableau croisé dynamique - Épisode 2197 - YouTube; Excel - Moyenne des lignes non nulles dans un tableau croisé dynamique - Épisode 2538 - YouTube.
They gave me the idea to change my "daily consumption" formula in my original table and to swap my empty texts ("") for zeros (0) in this function. This allowed me to have a column full of numbers instead of made almost exclusively of empty texts and some numbers (which for some reasons bothered DAX FILTER and MEDIANX functions). I then proceeded to create my new Median measure in my pivot table with the following formula : =MEDIANX(FILTER(MyTable;VALUE(MyTable[DailyCons])<>0);MyTable[Daily Cons]). It appears the VALUE formula was required for Filter and Median to understand my column consisted of numbers and not texts.
This now works perfectly fine and I hope this self-answered thread may help someone else in the future. If not, then you may at least look at the videos I linked in here, because I consider them of interest for anyone who wants to learn by themselves.

This thread may now be closed by moderators.