Dynamic Quartile based on Date Slicer Power BI

0
votes

I have a sales table where I want to initially summarize by the ship to customer name in the temp table GroupTable. From there I want to return the 1st quartile value but the issue I am having is that I would like to use PERCENTILE.INC not PERCENTILEX.INC however I was not able to find a workaround considering my table I am referencing is a temp table. My end goal is to have dynamic quartiles based on a date slicer where I do not have to create a new static table.

Quantile1_Sales = 
VAR GroupTable =
    SUMMARIZE (
        'Sales',
        'Sales'[Customer Ship To],
        "Sales2", SUMX ( 'Sales', 'Sales'[Sales] )
    )

RETURN
   PERCENTILEX.INC ( GroupTable, [Sales2], .25 )

Here is an example of what the Sales table looks like below.

enter image description here

1
powerbidax
Can you share a sample dataset and and output in tabular form? – Angelo Canepa
I added an example of what the sales table looks like. – MLS

1 Answers

0
votes

You should calculate your table inside the PERCENTILEX function.

See the calculation below where it takes all the selected values for Customer Ship To and Date.

Calculation: Measure

Quantile1_Sales =
PERCENTILEX.INC (
    ALLSELECTED ( 'Table'[Customer Ship To], 'Table'[Date] ),
    CALCULATE ( SUM ( 'Table'[Sales] ) ),
    0.25
)

Output

enter image description here

Table Reference: Table

Date Customer Customer Ship To Sales
01 December 2020 Customer A CustA1 100
01 December 2020 Customer A CustA1 200
30 December 2020 Customer B CustB1 500
01 January 2021 Customer C CustC2 300
01 January 2021 Customer D CustD1 100
02 January 2021 Customer D CustD2 150
04 January 2021 Customer A CustA2 200
01 May 2021 Customer D CustD1 100
01 June 2021 Customer F CustF2 50