Table with Totals by period

[Delphia]

Hello everyone,

need your help with creating a table with totals like this:

I've created measures:

Bill ($) = SUM(Transactions[Bill])

But if I create a matrix, I can use them only as Columns.

But I need a view like on the 1st image above. 

What is the best way to do it?

Thank you in advance for your help!

[v-eqin-msft · Accepted solution]

Hi @Delphia ,

Based on my test, you could create a new table for period type like this( The index column is used to sort)

Then use the following formula to create a measure:

Measure =
SWITCH (
    MAX ( 'Period Type'[Period] ),
    "Last Week",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Calendar'[Date], TODAY (), -7, DAY ),
            FILTER ( 'Table', 'Table'[Occupation] = MAX ( 'Table'[Occupation] ) )
        ),
    "Last 4 Weeks",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Calendar'[Date], TODAY (), -28, DAY ),
            FILTER ( 'Table', 'Table'[Occupation] = MAX ( 'Table'[Occupation] ) )
        )
)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-eqin-msft · Accepted solution]

Hi @Delphia ,

In my data sample, yes, creating a new table is not very necessary unless you want to filter data with slicer or some other conditions. But in this case, you need to create many measures.

Last Week = CALCULATE(SUM('Table'[Value]), DATESINPERIOD('Calendar'[Date],TODAY(),-7,DAY),FILTER('Table','Table'[Occupation]=MAX('Table'[Occupation])))

Last 4 Weeks = CALCULATE(SUM('Table'[Value]), DATESINPERIOD('Calendar'[Date],TODAY(),-28,DAY),FILTER('Table','Table'[Occupation]=MAX('Table'[Occupation])))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Delphia]

Thank you so much for your help!

[v-eqin-msft · Accepted solution]

Hi @Delphia ,

Based on my test, you could create a new table for period type like this( The index column is used to sort)

Then use the following formula to create a measure:

Measure =
SWITCH (
    MAX ( 'Period Type'[Period] ),
    "Last Week",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Calendar'[Date], TODAY (), -7, DAY ),
            FILTER ( 'Table', 'Table'[Occupation] = MAX ( 'Table'[Occupation] ) )
        ),
    "Last 4 Weeks",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Calendar'[Date], TODAY (), -28, DAY ),
            FILTER ( 'Table', 'Table'[Occupation] = MAX ( 'Table'[Occupation] ) )
        )
)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Delphia]

Thank you for your help. It works with table. Nevertheless, I don't have a separate table with these values. I use measures in my calculations:

For example:

Billable (hours) = SUM(Transactions[Billable_hours])

How do you think: is it possible to avoid creating a new table and use measures? If not, could you please suggest me what is the best way to create it?

[Fowmy]

@Delphia 

When you use a matrix visual, you have one option "Show on Rows" under Values section, you can turn it on and place the category in the columns to get the desired layout.

[Delphia]

Thank you so much for your advice! But how can I make that in rows it will be description of value (hours, $, margin, etc) and on the columns time period (last month, last 4 months, last year, YTD, etc.)? Should I create another type of values? Or it should be another table (summerized?)?