Matrix Visual & Dynamic Table & Dynamic Measures

[Anonymous]

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:

Selected Dimension IS

Dimension Selected IS

PLEASE HELP 😊

@amitchandak 

[Anonymous]

hi @amitchandak 

could you possibly advise on this one, please?

[DataInsights]

@Anonymous,

This link discusses dynamically switching dimensions:

https://www.thedataschool.com.au/danica-hui/switching-dimensions-in-power-bi/