Search This Blog

Tuesday, May 12, 2020

Ranking within a Group with Power Query

There's not really a ranking feature within Power Query like RANK.EQ or the DAX function RANKX. However that should stop you from ranking values in Power Query. That's the beauty of Excel...there's just many different ways to do similar things and this video showcases how you can rank stuff using Power Query.

Thursday, May 7, 2020

Calculate Median in PivotTable

Pivot Tables are one of the greatest analytical tools in Excel for it's capability to slice and dice data. It can summarize data by sum, average and even standard deviation. But the one of the basic statistical measures is the median and unfortunately that is missing from the pivot table options. Fortunately you can include that option in and it just involves creating the pivot table and adding it into the data model to write a simple DAX measure. See how it works by watching this short video.

Tuesday, May 5, 2020

Ranking within Groups with Power Pivot

Ranking items is fairly easy to do in Excel whether you're doing it for a table or in a pivot table, but when it comes to showing ranking within groups, you might need to use some combination of functions. This videos show how it can be done with DAX functions (like RANKX and CALCULATE) in Power Pivot. It's actually not too complicated and once you get the hang of using it, the results are pretty neat!