Matrix Visual & Dynamic Table & Dynamic Measures

Anonymous · ‎11-27-2020

Hi

I am using a matrix visual to present an income statement. I would like to have the values of a chosen dimension as the columns in the matrix visual. I am struggling to pull in my measure that I created into the column part of the visual, I think due to my DAX not being correct. So I NEED HELP with DAX I think.

My data:

Dimensions (tables): Product, Company

Dimension values:

ProductID…

productname – product A, product B, product C

CompanyID..

companyname – company A, company B, company C

Fact Table: Columns for Attribute, product(id), company (id), Amount/Value

Attribute is: Revenue, Cost of Sales etc

Thus visual would look like this:

if Product was selected:

Attribute Product A | Product B | Product C | Grand Total

Revenue 10 5 5 20

Cost of Sales 3 2 1 6

if Region was selected:

Attribute Company A | Company B | Company C | Grand Total

Revenue 10 5 5 20

Cost of Sales 3 2 1 6

So far, I have a Table which has one column which contains a list of all the Dimensions and sub dimensions that could be selected i.e.
Product, Companies etc – see below the table

NEED – how can I modify my IS DIMENSION to replace the Product, and Product A, B, C with the actual table eg DIM_Product and DIM_Product[Productname], DIM_Companies and DIM_Companies[Companyname] ? or can I use DAX otherwise?

NEED – which DAX do I need as measure to pull into matrix visual to respond dynamically to the selection of IS Dimension – eg selection of product or company must have effect that product A, B, C is shown as columns?

NEED – if I want two filter visuals (IS Dimension and IS Dimension – sub) and I want the IS Dimension – sub to respond to IS Dimension selection, which DAX do I need? i.e. I want IS Dimension sub filter visual to show product A, B, C when Product is selected on IS Dimension filter visual

This is my actual situation at the moment:

PLEASE NOTE – Gross Written Premium below is basically “Revenue” etc. these are incomes and expenses for insurance business.. and the columns as you see them there is Product A, B, C etc

Measures I have created so far: