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

0
votes

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?

1
powerbidaxpowerpivot
Is the screenshot of the raw data in your data model or a matrix generated from the raw data? Sharing the code for the measures you have working so far would also be helpful. – BarneyL
This is the raw data. The helping columns mentioned are the columns "Max 19", "Max 20", "Max 21" and so on. – Stack_Javi
I used some excel formula for each year :=MAX(IF(RIGHT(Table24[[#Headers];[Dep]:[Dec. 22]];2)=RIGHT(AT$2;2);Table24[@[Dep]:[Dec. 22]];0)) ; then with power query the max values were available. – Stack_Javi
Solved it. Key was to transform data such that A and C are in two columns. – Stack_Javi

1 Answers

0
votes

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.