Forum Discussion
matrix report
I have a matrix report with products on the rows, location on the columns and actual sales as the values
the above is simple to create in power bi
what my challenge is, the user then asked to display the forecast amounts at the end and bottom of the matrix.
so the user is just seeing total forecast amount for product a and the total forecast amount for location 1 and not the detailed
forecast amount for location1/product a
below is a sample of what the report needs to look like.
Hi Anonymous ,
The visual construct in the screenshot in the screenshot with the result you want should be a matrix, and as far as I know, Power BI requires us to add the specified value (like forecast) to the matrix value field before it will generate a subtotal for that specified value. So if we need matrix show subtotal for forecast and actual, we need to add both of them into value field.
According to your statement, the user want is to show forecast subtotal for product right beside the actual subtotal and show forecast subtotal for location below the actual subtotal. I think this is impossible. Due to if Power BI show forecast subtotal for product right beside the actual total, you may create a matrix as my above reply. We can see that we will add forecast and actual in value field. They should be in same level. In matrix values above and below should be in different hierachy levels. This is contradictory to the above.
So Power BI will show forecast subtotal for location right beside the actual subtotal as well.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
I think forecast should be a column in your data model, if your table looks like as below, you couldn't get result as you want. Autual sales and forecast sales should be in same level. So we couldn't show forecast sales total under actual sales total.
I suggest you to create a matrix as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks rico. your solution is not really what the user asked for. he doesn't want to see the forecast amount by location. he want actual by location , and then the total for actuals and to the right of this total the total for forecast.
so one column of forecast at the end of the matrix and a total of forecast as a row at the bottom of the matrix.
Hi Anonymous ,
The visual construct in the screenshot in the screenshot with the result you want should be a matrix, and as far as I know, Power BI requires us to add the specified value (like forecast) to the matrix value field before it will generate a subtotal for that specified value. So if we need matrix show subtotal for forecast and actual, we need to add both of them into value field.
According to your statement, the user want is to show forecast subtotal for product right beside the actual subtotal and show forecast subtotal for location below the actual subtotal. I think this is impossible. Due to if Power BI show forecast subtotal for product right beside the actual total, you may create a matrix as my above reply. We can see that we will add forecast and actual in value field. They should be in same level. In matrix values above and below should be in different hierachy levels. This is contradictory to the above.
So Power BI will show forecast subtotal for location right beside the actual subtotal as well.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.