cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RDLOPEZ Frequent Visitor
Frequent Visitor

Trying to remove "Prior Month Variance" from Totals in matrix.

I created a Matrix for a financial report (Can't upload this info for security reasons) showing a full year with monthly totals and prior month variance. Everything looks great but the Row Totals are also showing a Total and another cell for variance. I don't need the variance in the total column only in the monthly columns but still need the yearly total.  Is there a way to keep the variance for the monthly columns and remove it from the total column? I've looked all over the web and can't for the life of me find a solution to this issue. 

Thank You in Advance,
Ray Lo the Data Bro

9 REPLIES 9
natelpeterson Senior Member
Senior Member

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

Hi @RDLOPEZ,

You could use the following pattern:

IF(
HASONEVALUE('Date'[Month]),<do your calculation>,BLANK()
)
Hope this helps,
Nathan

 

RDLOPEZ Frequent Visitor
Frequent Visitor

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

Thank you @natelpeterson  so much for the assistance. That ended up working for a majority of the variances but for some reason i'm still getting a handful of them still showing up. Here is the the formula I have going right now 

Prior Month Variance =
IF(
HASONEVALUE(Finance[Posting Date.Month]),
IF(
    ISFILTERED('Finance'[Posting Date.Month]),
    VAR __PREV_MONTH =
        CALCULATE(
            SUM('Finance'[Current Total]),
            DATEADD('Finance'[Posting Date.Month].[Date], -1, MONTH)
        )
    RETURN
        DIVIDE(SUM('Finance'[Current Total]) - __PREV_MONTH, __PREV_MONTH)
),BLANK())

Is there something I'm missing that would allow most to go away but still leave a few in place? 

None of them report back the correct info on the totals column and all show a 0% variance. 

I've also noticed that all of the totals that still show the varaince percentage only have a month of data showing (Not sure if that might have anything to do with it). 
 
natelpeterson Senior Member
Senior Member

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

Hi @RDLOPEZ ,

I'm not sure exactly what's happening - could you mock up what you're trying to do?

 

The pattern I provided should prevent the calculation from running if there is more than one month involved in the calculation. It's the same idea as ISFILTERED - I assumed the total meant something like the entire year.

 

Thanks,

Nathan

 

RDLOPEZ Frequent Visitor
Frequent Visitor

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

@natelpeterson here is a small snip of what i'm working on. 

It's a massive breakdown done by monthly totals and having the yearly totals at the end of the report. But I don't need the monthly variance in the grand total for the year.

Some of these rows only have payments in a single month some have them for every month. So when I used your formula it removed everything but the rows where only one month had a subtotal. 

 

Capture.JPG

natelpeterson Senior Member
Senior Member

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

@RDLOPEZ - Could you provide a screenshot of the fields that are in use on your matrix?

Thanks,

Nathan

RDLOPEZ Frequent Visitor
Frequent Visitor

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

Capture1.JPG

natelpeterson Senior Member
Senior Member

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

@RDLOPEZ - I think it would be helpful to:

1. Add a Date Dimension table in your model.

2. Create a Relationship between the new table and your Fact table.

3. Mark your table as a Date Table.

4. In any calculations and visuals, reference the date attributes in the date table. For instance, there will be an attribute called Month.

5. (Additionally) It is also preferable to have all Dimension information separated into their respective tables. e.g. company info all in it's own dimension table and then join the fact table on the key. One benefit is that it makes your fact, which tends to be tall, thinner. 

Hope this Helps

 

RDLOPEZ Frequent Visitor
Frequent Visitor

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

@natelpeterson i'm confused as to how that will help me to rid of the % from the yearly grand total at the end of my report. The report itself is all good other than removing that Grand Total percetage. 

natelpeterson Senior Member
Senior Member

Re: Trying to remove "Prior Month Variance" from Totals in matrix.

@RDLOPEZ - The reason that the grand total still shows up for rows that only have one month:

 

HASONEVALUE checks to see whether the attribute has only a single. In the problem case, the date column only has a single value for month, because it is filtered by the other dimensions.

 

If the date were in a separate dimension table, that table would not be filtered by other dimensions, and HASONEVALUE would be false.