Power BI DAX : Divide 2 Rows Data to derive 3rd Row

Question

We have table with the data as shown below :

Region,State,Date,Metric,Amount R1,S1,Jan 01 2010, Sales,28223 R1,S1,Jan 01 2010, Qty, 230 R1,S2,Jan 01 2010, Sales,28223 R1,S2,Jan 01 2010, Qty, 230

We would like to have a third metric to be computed as a separate row. When the user tries to build a report at a Region Level , then it should aggregate the data and show all the 3 metrics ( 2 Available and 3rd computed)

3rd Metric formula - Price Per Qty: Value for Metric Sales/Value for Metric Qty.

We would like to see the data in the format below :

Region,State,Date,Metric,Amount R1,S1,Jan 01 2010, Sales,28223 R1,S1,Jan 01 2010, Qty, 230 R1,S1,Jan 01 2010, Price Per Qty, 28223/230 R1,S2,Jan 01 2010, Sales,3452 R1,S2,Jan 01 2010, Qty, 122 R1,S2,Jan 01 2010, Price Per Qty, 3452/122

Suppose the user tries to build a report at Region Level , then the report should be as shown below :

Region,Metric,Amount
R1,Sales,sum(28223+3452)
R1,Qty,sum(230+122)
R1,Price Per Qty , Sum(28223+3452)/sum(230+122)

Any help is highly appreciated.

Answer 1

I'm not sure if this can be done without transforming the table. The 1st step is to pivot on Metric column. Choose the Amount column as values and click OK. Apply the changes.

The 2nd step is to create a measure to calculate Price per Qty. Here's the DAX.

Price per Qty = DIVIDE(SUM(Metrics[ Sales]),SUM(Metrics[ Qty]),BLANK())

The 3rd step is to use a Matrix visual instead of Table visual. Matrix visual allows you to show values on rows. The option is under Format pane -> Values -> Show on rows. You can use +/- icons to expand/collapse to the desired level and the metrics will be calculated for that level.