Forum Discussion
Separated Two Row Headers in Matrix
I have two fact tables of FctMainSales and FctDistributorSales connected to dimDatetable and dimCustomer. Both Fact Tables have Distributor Code which is connected to dimCustomer and FctDistributorSales has further sales data to outlet by the distributor. I want to show Sales Data as follows: Is it possible to create in Power BI?
| Distributor Name | Collection A | Collection B | Total | Outlet Name | Collection A | Collection B | Total |
| Ditributor A | 100 | 101 | 201 | 800 | 300 | 1100 | |
| Outlet B | 500 | 200 | 700 | ||||
| Outlet C | 300 | 100 | 400 | ||||
| Ditributor B | 500 | 400 | 900 | 1075 | 732 | 1807 | |
| Outlet D | 227 | 270 | 497 | ||||
| Outlet E | 394 | 217 | 611 | ||||
| Outlet F | 454 | 245 | 699 |
Hi peterpan
Use one Matrix with Distributor then Outlet as rows and turn Stepped layout off. In the model, link DimDate and DimDistributor to both fact tables, and link DimOutlet only to FctDistributorSales. Keep Distributor subtotals on and Outlet subtotals off. Create measures that switch by level using ISINSCOPE so Outlet rows read from FctDistributorSales and Distributor and Grand Total read from FactMainSales,for example Collection A = IF(ISINSCOPE(DimOutlet[Outlet]), SUM(FctDistributorSales[CollectionA]), SUM(FactMainSales[CollectionA])); do the same for Collection B and set Total = A + B. If you want a second subheader after the first two columns, add a disconnected Band table in Columns and return values only for the matching band.
6 Replies
Hi peterpan
Yes,build one Matrix: put Distributor then Outlet in Rows, turn Stepped layout = Off to get two separate row-header columns, and use ISINSCOPE-based measures so the same measures return totals from FctMainSales at the Distributor/Grand Total level and outlet values from FctDistributorSales at the Outlet level (then Total = A + B); keep Distributor subtotals on and Outlet subtotals off, with a simple star model (DimDate & DimDistributor to both facts; DimOutlet only to FctDistributorSales).
Thanks for the input. This is similar but can we create second subheader column after first two values of Collections? Also, it would be beneficial since the outlet values will have a subtotal which won't match with distributor sales value
Hi peterpan
Use one Matrix with Distributor then Outlet as rows and turn Stepped layout off. In the model, link DimDate and DimDistributor to both fact tables, and link DimOutlet only to FctDistributorSales. Keep Distributor subtotals on and Outlet subtotals off. Create measures that switch by level using ISINSCOPE so Outlet rows read from FctDistributorSales and Distributor and Grand Total read from FactMainSales,for example Collection A = IF(ISINSCOPE(DimOutlet[Outlet]), SUM(FctDistributorSales[CollectionA]), SUM(FactMainSales[CollectionA])); do the same for Collection B and set Total = A + B. If you want a second subheader after the first two columns, add a disconnected Band table in Columns and return values only for the matching band.
Hi peterpan,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to rohit1991 for prompt and helpful responses.
Just following up to see if the solutions provided by community member were helpful in addressing the issue.
If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarHi peterpan,
Just following up to see if the response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
Best regards,
Prasanna Kumar
Hi peterpan,
Just following up to see if the solutions provided by community member were helpful in addressing the issue.
If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna Kumar
