DAX formula to calculate Median and Percentile Q1 and Q3 from multiple column

[andigaruda]

Hello Experts,
I need your expertise here.

I am new in DAX formula and I am working ini some data where I need to calculate Median and Percentile Q1 and Q3 with mutiple colums as a source of data . In this case Data1 Data 2 Data 3
I have tried to use median and median x formula ; but still fail 
Thanks for the help.

Table data as follow : 

Month	Week	Date	Data1	Data 2	Data 3
Sep	3	21/09/2019	1,39	1,36	1,39
Sep	3	21/09/2019	1,38	1,32	1,38
Sep	3	21/09/2019	1,39	1,34	1,39
Sep	3	22/09/2019	1,39	1,34	1,39
Sep	4	23/09/2019	1,3	1,34	1,3
Sep	4	24/09/2019	1,4	1,38	1,4
Sep	4	25/09/2019	1,36	1,39	1,36
Sep	4	26/09/2019	1,34	1,38	1,34
Sep	4	27/09/2019	1,32	1,37	1,32
Sep	4	28/09/2019	1,32	1,39	1,32
Sep	4	29/09/2019	1,3	1,37	1,3
Sep	5	30/09/2019	1,39	1,31	1,39
Oct	1	01/10/2019	1,3	1,35	1,3
Oct	1	02/10/2019	1,38	1,33	1,38
Oct	1	03/10/2019	1,39	1,38	1,39
Oct	1	04/10/2019	1,35	1,4	1,35
Oct	1	05/10/2019	1,36	1,31	1,36

[sturlaws]

Hi @andigaruda ,

is it a requirement to keep the table with that structure? I ask because if you use the "Unpivot Columns"-functionality of Power Query/Edit Queries, you will have one column which identifies which data set it is, and one column with all the values. And since there is one column with all the values, calculating e.g. the median will become trivial

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[andigaruda]

Dear Sturlaws,

Thank you for your response and propose solution,  Unfortunately the table is required using that structure .

That is giving me a headache . 🙂

Best regards

[sturlaws]

@andigaruda,

I got this to work for your sample dataset:

median =
VAR allDatasets =
    UNION (
        SELECTCOLUMNS ( 'Table'; "Data"; 'Table'[Data1] );
        SELECTCOLUMNS ( 'Table'; "T2"; 'Table'[Data 2] );
        SELECTCOLUMNS ( 'Table'; "T3"; 'Table'[Data 3] )
    )
VAR addBreakTies =
    ADDCOLUMNS (
        allDatasets;
        "Data+rand"; [Data]
            + RANDBETWEEN ( 1; 1000 ) / 1000000
    )
VAR addRank =
    ADDCOLUMNS (
        addBreakTies;
        "Rank"; RANKX ( addBreakTies; [Data+rand];;; DENSE );
        "NumberOfValues"; CALCULATE ( COUNTROWS ( allDatasets ); ALL ( 'Table' ) );
        "midPoint";
        VAR nov =
            CALCULATE ( COUNTROWS ( allDatasets ); ALL ( 'Table' ) )
        RETURN
            IF ( MOD ( nov; 2 ) = 0; DIVIDE ( nov; 2 ); DIVIDE ( nov - 1; 2 ) )
    )
VAR _median =
    SUMMARIZE ( FILTER ( addRank; [Rank] = [midPoint] ); [Data] )
RETURN
    _median

But will depend on how you want your report to look like. If you can provide a mockup of how you want your report to look like(e.g. in excel), I can advise a more spesific solution

Out of curiosity, why do you have to keep the table in this structure?

Cheers,
Sturla

[andigaruda]

Dear Sturla, 
Thank you for your help.
The goal is to create a box and whisker plot over the time ( Monthly ; Weekly & Daily ) to  show the shape of the distribution, its central value, and its variability to the operator level as a process owner as shown below .

Table structure is derived form the actual form used by operator to record the data,  
With this structure . we hope that operator easily identify the abnormal value of range between Data 1 Data 2 and Data 3

Best Regard

[sturlaws · Accepted solution]

I can see how that structure will be helpfull while gathering the data, but I can't see the reason why you should not reorder the data in Power BI. With reordered data, you can still achieve a visualisation correpsponding to the input by using the matrix visual, with dataset name as column header. And at the same time, it will make your DAX code much less complicated.

[andigaruda]

Dear Sturla
Thank you for your help and suggestion 
Have a nice day