Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Variance in Matrix Sub Total/Total or Column?

Hi,

 

First time posting here so hopefully I've added all the relevant details for help. I'm at the end of my tether with this, I've tried everything. I think I'm almost there (and I don't think I can progress further!).

 

I've got a matrix like so:

 

 

Each row is a calculation that returns values. The columns are the years.

 

As I want to compare year on year performance for ANY date selected (from date slicer) I take the min/max DDMM selected and filter on the calendar table joined to my facts (1-* with ref integrity).

 

Example of the values you are seeing:

 

Measure1 = VAR maxDayMonth=MAX('zz - Report Driver Date'[MonthDayKey])           VAR minDayMonth = MIN('zz - Report Driver Date'[MonthDayKey])          VAR currentYear=YEAR(TODAY())            VAR lastYear=(YEAR(TODAY())-1)
RETURN
IF(HASONEVALUE('Reporting Date'[Year]), 
// Return calculation for the year as HASONEVALUE() = TRUE
CALCULATE(DISTINCTCOUNT('Fact'[Fact_Id]), 'Dim1'[Dim1Value] = "Inbound"
, NOT('Dim2'[Dim2Value] IN {"Value1","Value2"}), 'Reporting Date'[MonthDayKey] >= minDayMonth
,'Reporting Date'[MonthDayKey] <= maxDayMonth, NOT('Dim3'[Dim3Value] = "")
, 'Dim4'[Dim4Value] = "Sales")

// Does not have one value, therefore we are looking at Total Column
,
CALCULATE(DISTINCTCOUNT('Fact'[Fact_Id])
// Filters
,'Dim1'[Dim1Value] = "Inbound"
,NOT('Dim2'[Dim2Value] IN {"Value1","Value2"})
,'Reporting Date'[MonthDayKey] >= minDayMonth
,'Reporting Date'[MonthDayKey] <= maxDayMonth
,NOT('Dim3'[Dim3Value] = "")
,'Dim4'[Dim4Value] = "Sales"
,'Reporting Date'[YearCalendar] = currentYear)

/

CALCULATE(DISTINCTCOUNT('Fact'[Fact_Id])
// Filters
,'Dim1'[Dim1Value] = "Inbound"
,NOT('Dim2'[Dim2Value] IN {"Value1","Value2"})
,'Reporting Date'[MonthDayKey] >= minDayMonth
,'Reporting Date'[MonthDayKey] <= maxDayMonth
,NOT('Dim3'[Dim3Value] = "")
,'Dim4'[Dim4Value] = "Sales"
,'Reporting Date'[YearCalendar] = lastYear)

)

Hopefully you can see that the logic is as follows:

 

For each year column:

  • get the Min/Max DDMM of the Dates Selected
  • Calculate the distinct number of rows on the fact table (for volumes) where the dates of the fact occuring are between the DDMM of the date selected
  • Filter Out on certain dim values

For the Subtotal

  • HASONEVALUE() returns false as there are multiple years
  • instead, using the varialbes CurrentYear & PreviousYear, calculate the volumes and divide one by the other

Now this works (and I was so surprised it did!). The issue now is that the variance is a % and my actual measure is an Integer/Decimal in places.. Does anybody know how I can change the subtotal data type? Is that even possible?

 

I know I'm incorrectly using the subtotal/total columns, so is there a better way to go about calculating the variance at the end?

 

Thank you.

 

 

Need Help

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    All function should help you in your sceanrio. It returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

     

    Such as :

     

    CALCULATE (
    DISTINCTCOUNT ( 'Fact'[Fact_Id] ),
    FILTER (
        ALL ( 'Reporting Date' ),
        'Reporting Date'[MonthDayKey] >= minDayMonth
            && 'Reporting Date'[MonthDayKey] <= maxDayMonth
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the reply. I'm not sure how this will help with my problem? I'm trying to calculate the variance of the two specific date columns. I'm already able to calculate this - my issue is the formatting at the end.

       

      Alternatively, is there a better way to use a matrix to add a variance column on the end?