Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 di...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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 Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.