Forum Discussion

vkarthik21's avatar
vkarthik21
Frequent Visitor
7 years ago
Solved

Adding Measure as dimension to existing group

I have the following data - 

 

ProductName,State,City,GST,Amount,Quantity
iPhone,Andhra Pradesh,Guntur,5,100,30
iPhone,Karnataka,Bangalore,10,80,20
Samsung,Andhra Pradesh,Guntur,5,75,10
Samsung,Karnataka,Bangalore,10,80,10
Xiaomi,Andhra Pradesh,Guntur,5,50,10
Xiaomi,Karnataka,Bangalore,10,30,5

 


I need to develop a matrix report with State, City as Rows and Amount, Quanity as Columns.  

On top of each sub group total at State level, GST row should appear and a new calculated measure Gross Total should come.

 

For example, say for Andhra Pradesh, the total Amount is  225, GST is 15, hence Gross would be = 225 + 15 = 240. 

 

Here is the output that I am expecting - 

 

 

Is this do-able? I have looked at the option of creating new dynamic table under City category but that would not give me the option for GST Total. Another way I wanted to approach the problem is by unpivoting on GST, State and try creating a join between the resultant table and 'State' table (derived automatically) but I am not able to formulate how to do. Am little new to Power BI. Could you kindly guide me how to do? 

  • Hi vkarthik21,

     

    Based on my test, it is not possible to meet your requirement currently. You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.

     

    Regards,

    Frank

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support

    Hi vkarthik21,

     

    Based on my test, it is not possible to meet your requirement currently. You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.

     

    Regards,

    Frank

    • vkarthik21's avatar
      vkarthik21
      Frequent Visitor

      Thanks Frank for the update. Figured that would be the case. Now need to see if we can rejig the reporting requrirement.