Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jderekc
Helper IV
Helper IV

Alternate Calculations in Matrix

Hi all,

 

I am curious if there is a way to accomplish what I am trying to do.  Please bear with me...  I have a matrix that has limited interactive options (search by vendor or product type).  This is fine and how it should work.  However, I need to compare year-over-year and come up with a difference row in the matrix.

 

So here's what I have:

 

supplierreportcalcissue.PNG

 

The problem is that the GP% column is using a formula to calculate gross profit, which is great for periods 2016 and 2015, but not great for "period" labeled "DELTA", where really all I want "DELTA" to do is show the difference between net sales year-over-year, gross profit year-over-year, and GP% year-over-year.

 

Is there a good way of doing this?  I'd like to keep the appearance as close to this as possible (meaning keep the calculations where they are and hopefully keeping the word "DELTA" where it is).

 

I have used a SQL statement in the query behind this report to generate all the numbers, including GP%, but then the matrix makes me sum up the values or choose another aggregate.  That doesn't work very well for the intended purpose.  I have also tried a bit of DAX, but I am not an expert on DAX beyond simple calculations and such.

 

Any advice or help would be greatly appreciated!

 

Regards,

Derek

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @jderekc,

 

You can refer to below formula to use row label to switch calculate formula:

% & Diff = 
var currentType=LASTNONBLANK('Sample'[Type],[Type])
return
SWITCH(currentType,"D",SUM('Sample'[Net Sales])-SUM('Sample'[Gross profit]),FORMAT(DIVIDE(SUM('Sample'[Gross profit]), SUM('Sample'[Net Sales]),0),"percent"))

 

Source table:

8.PNG

 

Notice: Type A,B,C calculate percent, Type D calculate diff.

 

7.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

Hi @jderekc,

 

You can refer to below formula to use row label to switch calculate formula:

% & Diff = 
var currentType=LASTNONBLANK('Sample'[Type],[Type])
return
SWITCH(currentType,"D",SUM('Sample'[Net Sales])-SUM('Sample'[Gross profit]),FORMAT(DIVIDE(SUM('Sample'[Gross profit]), SUM('Sample'[Net Sales]),0),"percent"))

 

Source table:

8.PNG

 

Notice: Type A,B,C calculate percent, Type D calculate diff.

 

7.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks a lot, v-shex-msft!

 

This is getting me on the right track.  Here's my DAX formula:  % & Diff =
var currentType=LASTNONBLANK('PeriodOrder'[Period], [Period])
return
SWITCH(currentType,"DELTA",SUM(BySupplier[NetSales])-SUM(BySupplier[GP]),FORMAT(DIVIDE(SUM(BySupplier[GP]), SUM(BySupplier[NetSales]),0),"percent"))

 

It returns the correct calculations for gross profit percent for 2016 and 2015 data (the data this report is looking at), but it still doesn't seem to affect the difference via "DELTA".  I'm trying different things but I keep getting results that seem to affect ALL gross profit percentages.

 

I think what it's doing is calculating the gross profit from "DELTA" in general and not looking at 2016 and 2015 data.  I need the difference to come from the previous two rows and not just calculate the percentage on the last row from the data in the prior columns of that same row.  Does that make sense?  Again, I think this is on the right track.

 

As always, I appreciate the help!

 

- Derek

HI @jderekc,

 

According to your description, you want to the GP% also works on total row, right?
If this is a case, you can add a conditional to check the total row and write the total row formula in it.

 

 

% & Diff =
VAR currentType =
    LASTNONBLANK ( 'PeriodOrder'[Period], [Period] )
RETURN
    IF (
        COUNTROWS ( BySupplier ) <> COUNTROWS ( ALL ( BySupplier ) ),//check total row
        SWITCH (
            currentType,
            "DELTA"SUM ( BySupplier[NetSales] ) - SUM ( BySupplier[GP] ),
            FORMAT (
                DIVIDE ( SUM ( BySupplier[GP] )SUM ( BySupplier[NetSales] )0 ),
                "percent"
            )
        ),
        "TotalRow Formula"
    )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

Check this out, it should get you to what you want.

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi smoupre!

 

Thank you for the comments.  I was playing around with that DAX function referenced in the thread you shared.  Unfortunately when I was using it, the expression evaluated for true for everything.  For instance: if I put a value of "400" if the expression is true, then all GP % values turn to "400", including "DELTA".  I think this might be because "DELTA" is a period like the other two (2016 and 2015).  I'm going to work with it more tomorrow to see if I can get it to work, but I think I may not have much luck because "DELTA" is exactly the same as 2016 or 2015.  It's a period assigned to a value (1--2016, 2--2015, 3--DELTA) and the SQL behind it all is doing the calculations for the differences.

 

So I am subtracting 2015 from 2016 values to get differences and that is written in SQL.  Maybe I should take that out of SQL and put it all in DAX?  The total row for the matrix sums up 2016 and 2015 but doesn't subtract them.  Perhaps I am missing something adundantly obvious.  It's a bit late now, so I apologize if I am missing something quite simple.

 

Regards,

Derek

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.