calculate difference between two rows in a matrix

[LUCASM]

I am recreating a set of excel charts and tables into PowerBi (which may be my problem) 

I have a number of tables which I am building as Matrixes and am having trouble with summing the results

Each matrix is quite small the columns ranging from April to March (as the financial Year)

and the rows are 2 for example 2018, 2019

the values are then a sum of the sales

What I am in need of help with is creating a 3rd row which for example gives the difference by subtracting September from August

and a 4th row which for example gives the %difference by dividing September from August - 1

Example of  Excel Table I am trying to replicate

Any help gratefully received

[LUCASM]

I should have added my Power Bi side

Products.Year

Products.Type

Calendar.PeriodMonth

Products.Units

[v-shex-msft]

Hi @LUCASM ,

You can consider adding measure formula with if statements to replace your value field.

Measure =
CALCULATE (
    CALCULATE ( SUM ( Table[Amount] ), Table[Type] = "Forecast(Sep)" )
        - CALCULATE ( SUM ( Table[Amount] ), Table[Type] = "Forecast(Aug)" ),
    ALLSELECTED ( Table ),
    VALUES ( Table[FisalDate] ),
    VALUES ( Table[Date] )
)

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

[LUCASM]

Hi @v-shex-msft 

This doesn't work for my needs

I have added a pibx file for anyone who is looking to assist me here

Kind Regards and thanks

 test file 

[v-shex-msft]

HI @LUCASM ,

You can refer to the below sample file if it is suitable for your requirement. (I do a few customize on your sample data; custom sort table, attribute table, relationship, measure formula...)

Measure =
VAR result =
    CALCULATE (
        SUM ( 'Table'[Volume] ),
        VALUES ( 'Row'[Type] ),
        VALUES ( Sort[Month] )
    )
RETURN
    IF (
        result <> BLANK (),
        result,
        VAR may =
            CALCULATE ( SUM ( 'Table'[Volume] ), 'Row'[Type] = "Forecast (May)" )
        VAR apr =
            CALCULATE ( SUM ( 'Table'[Volume] ), 'Row'[Type] = "Forecast (Apr)" )
        RETURN
            SWITCH (
                SELECTEDVALUE ( 'Row'[Type] ),
                "+/-", may - apr,
                "% Var", ( may - apr ) / may * 100,
                BLANK ()
            )
    )

Regards,

Xiaoxin Sheng

[LUCASM]

Hi @v-shex-msft 

Thank you for your assistance, this is looking very promising.

I do have 2 points that I need clarification on#

1. You have created a view of the Source data called Table with the exception of Attribute = Total - I understand why you did this
2.  You have created a new table called  Sort which pulls in the months and recreates them as dates in the format "mmm" This is joined to Table as a bidirectional many to 1 
3. You also created another new table in order to include the 2 new variables +/- and % Var  - I understand why you did this

In the new table called Table you have introduced a Measure to populate the variables +/- and % Var and here I unsure:

There are to variables mar and apr (which could be called anything) but they appear hardcoded to Forecast (Mar) and Forecast (Apr) - is my understanding here correct?, as this needs to be looking at whatever periods have been selected, these periods will change over time. Forecast (Dec) Forecast (Jan) for example.

Secondly, I cannot fathom out how you managed to sort the dates in the matrix (Jan to Dec)

Once I finished running through what you have done, I ended up with Alphbetical order (Apr, Aug, Dec, Feb, etc)

[v-shex-msft]

Hi @LUCASM ,

#1. Sort table is a custom table which I used to achieve custom sort order for month name, you can refer to the following link to know more about this:

Custom Sorting in Power BI 

#2. '% Var' and '+/-' which you mention not really existed in your table, so I create an expand table to store all original types and custom types and use on the matrix to expand type field.

#3. Yes, I hard code these types because your types are stored text value which not support dynamic lookup current and next values.  I improve my formula to extract month from type string and convert them to number, now these values can be dynamic changes based on extracted month number.

Measure = 
VAR result =
    CALCULATE (
        SUM ( 'Table'[Volume] ),
        VALUES ( 'Row'[Type] ),
        VALUES ( Sort[Month] )
    )
VAR list =
    ADDCOLUMNS (
        EXCEPT ( ALLSELECTED ( 'Row'[Type] ), { "+/-", "% Var" } ),
        "Index",
        VAR postion =
            SEARCH ( "(", [Type], 1, -1 )
        VAR lenth =
            IF ( postion <> -1, LEN ( [Type] ) - postion )
        RETURN
            IF (
                lenth <> BLANK (),
                MONTH ( DATEVALUE ( LEFT ( RIGHT ( [Type], lenth ), lenth - 1 ) & "/1" ) )
            )
    )
VAR _minList =
    MAXX ( FILTER ( list, [Index] = MINX ( list, [Index] ) ), [type] )
VAR _maxList =
    MAXX ( FILTER ( list, [Index] = MAXX ( list, [Index] ) ), [type] )
RETURN
    IF (
        result <> BLANK (),
        result,
        VAR _min =
            CALCULATE ( SUM ( 'Table'[Volume] ), 'Row'[Type] = _minList )
        VAR _max =
            CALCULATE ( SUM ( 'Table'[Volume] ), 'Row'[Type] = _maxList )
        RETURN
            SWITCH (
                SELECTEDVALUE ( 'Row'[Type] ),
                "+/-", _max - _min,
                "% Var", ( _max - _min ) / _max * 100,
                BLANK ()
            )
    )

Regards,

Xiaoxin Sheng

[LUCASM]

Hi @v-shex-msft 

Oh my gosh I am so close now.

Thanks for the additional help with custom sorting that was extreamly helpful.

The summing for some reason does not appear to be always working though,

If the two rows are called Forecast (mmm) the +/- and % Var then the calc works as expected 

it just returns 0 if both or one of the rows are not called Forecast (mmm)

Is there a way to step through the DAX code to see what the values are at each point of the user defined measure "Measure"

[LUCASM]

le refering to DAX Studio, and picked up some work done by Matt Allington but I can't see how this allows you to step through and see the various stages of the DAX measure (Measures)

I seem to have lost the ability to get any of the +/- and % Var codingto work no idea what going on now...

I have noticed that I nned to use some many to many relationships which are only available in the October2019 version of the PowerBI Desktop. Just to add insult to injury this October 2019 release cant be uploaded to the September release of Power BI Server and the next server release for us, is in January.

This does mean however I have until January to get this working properly......

I have a fuller pbx file here

If anyone has any more ideas I would be very grateful.

[LUCASM]

Hi

having worked on this continually for sometime now, I have seen others use an Index to attempt to solve their problem, but in all the cases I have come accross, this has been in a column.

My issue is that I need this in a row.

I have added Indexes in a "Sort Type" Table

and Indexes in a Sort Months table - This one was suggested by @v-shex-msft  which allowed me to sort my months by our financial year, an excellent tip 🍻.

After seeing that work, I hoped that the difference calculation would take a new turn, but it hasn't and I'm getting pretty vexed at my lack of DAX skills 😩

My understanding is that Measuers are a memory calculation and my output needs to be a matrix which gives the results of 2 filtered/selected rows: Row 2 - Row 1 ( also Row 2 / Row 1 - Row 2 to give a % change as a 4th row)

So im unclear if the selected filters on the page will be used???

I attempted the following thinking it would work, but it fails as apparently I am trying to use MIN & MAX as a True/False expression as a table filter which is not allowed

Diff = 
VAR result =
    CALCULATE (
        SUM ( 'Source'[Volume] ),
        VALUES( 'Sort Type'[Index] ),
        VALUES ( 'Sort Type'[Type] ),
        VALUES ( 'Sort Months'[Month] )
    )
RETURN
    IF (
        result <> BLANK (),
        result,
        VAR var_a =
            CALCULATE ( SUM ( Source[Volume] ), 'Sort Type'[Index] = MIN('Sort Type'[Index]) )
        VAR var_b =
            CALCULATE ( SUM ( Source[Volume] ), 'Sort Type'[Index] = MAX('Sort Type'[Index]) )
        RETURN
            SWITCH (
                SELECTEDVALUE ( 'Sort Type'[Index] ),
                "+/-", var_b - var_a,
                "% Var", ( var_b - var_a ) / var_b * 100,
                BLANK ()
            )
    )

Can anyone see through my drivel and suggest a working solution

How do you attach the file I've had to put a link in instead?

link to PIBX Test File October 2019 version 

[manesh_daniel36]

Hey I am having to do the same, did you find a solution for the same ?

[LUCASM]

Hi 
Sorry Ive only just seen your question, 

Sad to say no I didnt get a solution to this, I think my excel experience was overshadowing my Power BI limitations.

I gave up in the end on this part of the project.