Get MEDIAN from summarized column?

[macfuller]

I would like to get a median value for refund checks. My ERP data however lists checks multiple times in the table:

Voucher # Voucher $ Check # Check $
ABC $10 001 $20
DEF $10 001 $20
and so on.
So it is necessary to summarize the data in order to get a single row for each check # and check amount.

MEDIAN only accepts a column as an argument. I can create the SUMMARIZE data no problem.

[COLOR=#0000ff]SUMMARIZE[/COLOR] (
    Vouchers,
    [COLOR=#000000]Vouchers[Check Number][/COLOR],
    [COLOR=#FF0000]"Checks"[/COLOR], [COLOR=#0000ff]MAX[/COLOR] ( [COLOR=#000000]Vouchers[Payment Amount][/COLOR] )
)

But every attempt to encapsulate this inside a MEDIAN statement using ADDCOLUMNS or SUMMARIZECOLUMNS still comes back to tell me that MEDIAN requires a column as its argument.

Anyone have a solution for this? Thanks.

 

[VBA Geek]

Have you tried using MEDIANX?


Table =<br><span class="Keyword" style="color:#0070FF">ROW</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="StringLiteral" style="color:#D93124">"Median"</span>, <span class="Keyword" style="color:#0070FF">MEDIANX</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">SUMMARIZECOLUMNS</span><span class="Parenthesis" style="color:#969696"> (</span><br>            Vouchers[Check Number],<br><span class="indent8">        </span><span class="indent4">    </span><span class="StringLiteral" style="color:#D93124">"TotalCheck"</span>, <span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Vouchers[Check Amount] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span>,<br>        [TotalCheck] * <span class="Number" style="color:#EE7F18">1.0</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

 

[macfuller]

That's interesting. I added your measure as above with no problems in the data model window, dropped it into a pivot table, and got an error.

"The database driver does not include necessary capabilities and cannot be used with Excel..."

 

[macfuller]

To follow up I'm running Office 365 64-bit with 32GB of RAM so I should have enough horsepower to run this! The error wiped out my add-ins including Power Pivot but at least a simple reboot brought them back.

Manually listing and sorting all our 2018 YTD refund payments (24,900+) I can see that the median is $30 and the average is $175. It isn't until row 24,500 that the payments get over $1,000. That's a lot of checks going out for not much money.

Rather than the median I think it would be more useful to have a percentile chart as sought after here to give an idea of the overall payment profile since median isn't very revealing. But it looks like I need a supporting table to store the lookup percentile values as well as using Power Query to extract the distinct payments into a viable source since the SUMMARIZE above blows up my machine. But of course hard-coding the percentile table would mean it's not flexible for other filters and I'd have double the relationships in the model connecting to the summarized source table. Yikes!

 

[VBA Geek]

How are you adding it to the pivot table ?

 

[macfuller]

Drop from the field list window into the values box.

 

[VBA Geek]

Drop from the field list window into the values box.

my formula returns a table which cannot be used inside a pivot table. The formula to be used within the pivot table may vary depending on your data model and pivot table structure.

 

[macfuller]

Thanks. I'm realizing what a huge amount of processing median and percentile calculations take, so it may be necessary to hardcode specific filters rather than having them flexible via slicers.