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

[Anonymous]

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

[Anonymous]

Hi @Anonymous,

You could use the following pattern:

IF(

HASONEVALUE('Date'[Month]),<do your calculation>,BLANK()

)

Hope this helps,

Nathan

[Anonymous]

Thank you @Anonymous  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). 

[Anonymous]

Hi @Anonymous ,

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

[Anonymous]

@Anonymous 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. 

[Anonymous]

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

Thanks,

Nathan

[Anonymous]

[Anonymous]

@Anonymous - 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

[Anonymous]

@Anonymous 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. 

[Anonymous]

@Anonymous - 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.