Disconnected table with row and column totals

[AndyDD_UK]

In my fact table I have status and case numbers.

For example, this week's data might show status 2, 4, 5...and case numbers.

I want to create a list of all possible statuses (1 - 10) and show them as columns in the matrix regardless of whether there is data or not (can show as 0 where there is no data) and values are to be number of case numbers for each status. 

In addition, I want to be able to show column and row totals.

Rows to show subject from subject table which is joined to fact table.

How to do the above ? 

[v-menakakota]

Hi @AndyDD_UK   ,
Thanks for reaching out to the Microsoft fabric community forum. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards,  
Community Support Team  

[AndyDD_UK]

 Please see simplified data set: https://docs.google.com/spreadsheets/d/1QQECAhzN21QIHblLIB8pNljGBixKxB7MEEKOJ1L8eaU/edit?usp=sharing

1) Disconnected table showing regions (North, East, South, West and sort order)

2) Fact table. You'll note in the fact table that West does not exist in this date range.

Request:

How to show in table/pivot table all regions, where regions appear as columns? so in this case, west will appear but have 0 against it

How to show row and column totals ?

It's simplified but hopefully still good...

[v-menakakota · Accepted solution]

Hi  @AndyDD_UK  , 
Thanks for reaching out to the Microsoft fabric community forum.

To achieve your desired result of showing west in the matrix visual,

1) Please create a Measure called Total Sales

Total Sales = CALCULATE(SUM('Data Set'[Sales])) + 0

2) In the Matrix table add the Regions from Disconnected table in the columns tab, this will result in west appearing in the matrix visual.

Select the newly created Total sales in vales 
you will get the below output

3) Please make a note if you choose sales instead of the measure for the values tab, you will get the below output

after you select show items with no values.

So the point of the measure is to show the output in matrix for west as 0 instead of empty cell.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[v-menakakota]

Hi @AndyDD_UK ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Thank you.

[v-menakakota]

Hi @AndyDD_UK ,

We’re following up to check whether you were able to look into our earlier discussion. If you're still facing challenges, sharing a sample dataset will enable us to provide a focused and effective solution.

We’re here to help if you have any additional questions.

Thank you.

[v-menakakota]

Hi @AndyDD_UK ,

 If you're still facing challenges, sharing a sample dataset will enable us to provide a focused and effective solution.

We’re here to help if you have any additional questions.

Thank you.

[danextian]

Hi @AndyDD_UK 

I honestly can't grasp what you're trying to accomplish here. Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[AndyDD_UK]

Hi, thanks for reply...will get to the dataset bit later as out...but anothe example might be...

in fact table, have 2 regions, eg north and south...and sales 

in matrix want to show all regions (north, south, east and west) as columns, sales as values and products as rows.

And to show row and column totals.