Matrix Table - Using a different column for the subtotal by quarter

[Anonymous]

This is a bit of a quirky situation and I will spare you the details. An example of my table is as follows:

MONTH  EPS MTD  EPS QTD  EPS YTD

JAN	0.1	0.1	0.1
FEB	0.15	0.25	0.25
MAR	0.2	0.46	0.46
APR	0.25	0.25	0.71
MAY	0.3	0.64	1.01
JUN	0.35	0.89	1.38

Note: The QTD and YTD values do not neccesarily align 1 to 1 with the MTD (sparing details, there are adjustments made outside of the MTD numbers occasionally)

I am looking to build a matrix table to looks as follows:

Q1                                                          Q2

Jan	Feb	Mar	Total	Apr	May	Jun	Total	YTD Total
0.1	0.15	0.2	0.46	0.25	0.3	0.35	0.89	1.38

- The Quarterly total I want to come from my QTD column in my source table (i.e. Q1 total is the Mar QTD value)- The Monthly numbers i want to come from the MTD column in my source tabke

- The Grand total i want to be the current YTD Total (i.e. the Jun YTD Value)

I know it is a bit odd since the values of the months might not add up to the total, but this is something that will help the organization.

I am open in any way to modifying using power query to make the data work easier, I am just not sure there is a simple solution to this.

Thanks!

[jdbuchanan71 · Accepted solution]

@Anonymous 

You should be able to use ISINSCOPE to check what "level" you are at (month, qtr, year) and sum the right column based on that.

Measure = SWITCH (
    TRUE(),
    ISINSCOPE(Dates[Month]),SUM(YourTable[Month]),
    ISINSCOPE(Dates[Quarter]),SUM(YourTable[Quarter]),
    ISINSCOPE(Dates[Year]),SUM(YourTable[Year]),
    SUM(YourTable[Month])
)

You just need to decide what to do for the grand total.  In mine, I did the month column but you probably want the year again.