Best approach to creating table visual

[untapjoseph]

I am looking to understand the best approach for creating a table/matrix visual using a quite awkward data set. 

The output table needs to look something like this, as a broad guide of the rows and columns i need to show (seperate tables for two types, Alpha and beta):

Alpha

	Total	as a % of sales	Total Last Month	as a % of sales
Sales
Cost
Overheads
Refunds

Beta 

	Total	as a % of sales	Total Last Month	as a % of sales
Sales
Cost
Overheads
Refunds

The data set is similar to this example:

Area	Date	Sales	Cost	Overheads	Refunds
Alpha	Jan-21	2	1	1	1
Alpha	Feb-21	4	2	2	0
Alpha	Mar-21	6	3	3	1
Alpha	Apr-21	8	4	4	2
Alpha	May-21	10	5	5	1
Alpha	Jun-21	12	6	6	5
Alpha	Jul-21	14	7	7	0
Alpha	Aug-21	16	8	8	1
Alpha	Sep-21	18	9	9	2
Beta	Jan-21	2	1	1	1
Beta	Feb-21	4	2	2	0
Beta	Mar-21	6	3	3	1
Beta	Apr-21	8	4	4	2
Beta	May-21	10	5	5	1
Beta	Jun-21	12	6	6	5
Beta	Jul-21	14	7	7	0
Beta	Aug-21	16	8	8	1
Beta	Sep-21	18	9	9	2

Any advice on best practice within power query and/or on the building the table itself would be appreciated. Currently I am working on using calculated groups to filter the different types; the data itself has 35 different types, so would like to reduce the number of measures and speed up process! 

Any thoughts are welcome.

Many thanks

[BA_Pete]

cHi @untapjoseph ,

At first glance, your source data appears to be in a fairly efficient format for what you're trying to do, so no Power Query really required here. The only thing you might want to do to incease source efficincy would be to unpivot the [Sales], [Cost], [Overheads] and [Refunds] columns.

When you say "35 different types", are you talking about different [Area] values? If so, then you don't need to write specific measures for each [Area]. As your required ouput appears to be segmented by [Area] anyway, you can just write generic measures and apply a visual-level filter by [Area].

Pete

[untapjoseph]

Thanks Pete. yes, there 35 Areas, including alpha and beta. 

This is an example of one of the measures, which you can see currently filters the Area within the meaure. Hense, why i am trying to find a better way, to avoid replicating this measure for every area. I was then using the UNION and ROW functions to build the table,  which got me the format i was after but didn't allow me to use a visual-level filter by area...

Sales LQ =

VAR Current_Date = MAX(Data[Reference_Date].[Date])

VAR LM_Date = DATE(YEAR(Current_Date),MONTH(Current_Date)-1,DAY(Current_Date))

VAR RESULT = CALCULATE(SUM(Data[Sales])/1000000,

FILTER(Data,Data[Reference_Date]<=LM_Date),

FILTER(Data,Data[Area] = "Alpha"))

RETURN

RESULT

I am not looking into using calculation groups as a way to try to build the table using the matrix visual instead, do you think this would work?

[BA_Pete · Accepted solution]

@untapjoseph ,

Ok, you need a proper calendar table in your model. This will help enormously with the time intelligence you're trying to use, but also will isolate your date evaluations from context filters on your report.

Once you have your calendar table, then you can add a monthNumber column, like this:

//DAX
monthNumber = MONTH(calendarTable[Date])

//M
Date.Month([Date])

Then add a relativeMonth column, like this:

//DAX
(YEAR(calendar[Date]) * 12 + calendar[monthNumber])
- (YEAR(TODAY()) * 12 + MONTH(TODAY()))

//M
(Date.Year([Date]) * 12 + [monthNumber])
- (Date.Year(Date.From(DateTime.LocalNow())) * 12 + Date.Month(Date.From(DateTime.LocalNow())))

Relate your calendar table to your fact table on calendar[Date] ONE : MANY Data[Reference_Date].

You should then be able to write a generic measure, something like this:

Sales LQ =
CALCULATE(
  SUM(Data[Sales]) / 1000000,
  calendar[relativeMonth] <= -1
)

When you put that measure into your table, just filter the visual on [Area] = "Alpha" etc. and the date evaluation should be insulated from the filtering of your fact table.

Pete

[untapjoseph]

Thanks Pete, this helped alot. Once I had followed your guide, I then used calculated tables to bring through the current total, last month total and %age columns.