Forum Discussion
Helper IIIDisconnected table with row and column totals
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 ?
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 output3) 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
8 Replies
- danextian
Super User
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_UKHi, 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.
v-menakakotaCommunity Support
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 CommunityIf I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Community Support Team- 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.
- 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
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 output3) 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
