Search code examples
powerbidaxpowerpivot

Get Max and Max Difference with Power Pivot/ Power BI using DAX formulas

In this table, there are monthly values (A and C) for each Dep. What I am interesten in is 1) the max value per year for each A and C for each Dep; 2) the max difference between A and C for each year and each Dep

using DAX formulae in order to present results in pivot tables.

What I achived so far is the solution for 1) with helping columns showing the max for each year, however for the sake of not using explict helping columns, a pure DAX solution is desired.

For the second point I hadn't had any solution at all.

The table looks as follows: enter image description here

Is it possible to solve this in PowerPivot or Power BI using DAX?

Indeed, I transformed data in power query like this: enter image description here

And I used two DAX formulae:

Y_MaxX_Val_A:=CALCULATE(MAXX('Table2';'Table2'[Value]); ALL('Table2'[Month]) ;FILTER('Table2'; 'Table2'[A_C] = "A"))

Y_MaxX_Val_C:=CALCULATE(MAXX('Table2';'Table2'[Value]); ALL('Table2'[Month]) ;FILTER('Table2'; 'Table2'[A_C] = "C"))

which solves the first problem.

But how do I get the maximum difference of each year?

Solution

  • The data you're working with really isn't in a format that is helping here. If you're working through PowerPivot then I'd suggest restructuring it in PowerQuery while it's loaded in to the data model.

    Step 1: Unpivot all those date columns so you have one row per month. Step 2: Pivot on your A_C column so that you have an A column and a C Column Step 3: Ideally find a way to convert the monthly headers in to dates instead.

    You end up with data looking like this:

    Dep     Month   A   C
WLRRR   Jul-19  3   3
WLRRR   Aug-19  3   2

    Other tasks them become a lot easier as you just run MIN and MAX functions on columns to get your needed results.