Forum Discussion
Calculated Measure in Row
Not sure if this is even possible, but I have a data set like the following:
| Date | Attribute | Value |
| 1/1/2023 | Sales $ | 80 |
| 1/1/2023 | Sales U | 31 |
| 1/1/2023 | Cost $ | 40 |
| 1/2/2023 | Sales $ | 66 |
| 1/2/2023 | Sales U | 49 |
| 1/2/2023 | Cost $ | 20 |
| 1/3/2023 | Sales $ | 66 |
| 1/3/2023 | Sales U | 94 |
| 1/3/2023 | Cost $ | 25 |
I am trying to create a matrix like the chart below:
| Attribute | 1/1/2023 | 1/2/2023 | 1/3/2023 |
| Sales U | 31 | 49 | 94 |
| Sales $ | 80 | 66 | 66 |
| Cost $ | 40 | 20 | 25 |
| Margin | 50.0% | 30.3% | 37.9% |
In the chart, I have a calculated measure - Margin (Cost/Sales). My question is how can I bring each attribute into the rows, in addition to the calculated one?
Thanks!
Hi ChrisFromOhio ,
Please try the following methods and check if they can solve your problem:
1.Create a measure for Sales $.
Sales Amount = SUMX(FILTER('Table', 'Table'[Attribute] = "Sales $"), 'Table'[Value])2.Create a measure for Sales U.
Sales U = SUMX(FILTER('Table', 'Table'[Attribute] = "Sales U"), 'Table'[Value])3.Create a measure for Cost $.
Cost Amount = SUMX(FILTER('Table', 'Table'[Attribute] = "Cost $"), 'Table'[Value])4.Create a measure for Margin.
Margin = DIVIDE([Cost Amount], [Sales Amount], BLANK())5.Create a disconnected table that lists the attributes.
6.Create the conditional measure for display.
Display Value = SWITCH( SELECTEDVALUE('Table Ro'[Attribute]), "Sales U", [Sales U], "Sales $", [Sales Amount], "Cost $", [Cost Amount], //"Margin", [Margin], "Margin", FORMAT([Margin], "0.0%"), BLANK() )7.Drag the new table to the Rows in the matrix visual and Drag the conditional measure into the Values area.
8.The result is shown below.
Best Regards,
Wisdom Wu
7 Replies
Hi ChrisFromOhio ,
If I understand correctly, the issue is that you want to create a matrix visual. Please try the following methods and check if they can solve your problem:
1.Select the matrix visual in the visualization pane.
2.Drag the Date field to the Columns area of the matrix visual.
3.Drag the Attribute field to the Rows area of the matrix visual.
4.Drag the value field to the Values area of the matrix visual.
5.The result is shown in the below.
Looking forward to your reply.
Best Regards,
Wisdom Wu
Hi Wisdom - my question is how do I get the fourth attribute Margin (Cost/Sales) into the rows of the matrix?
Any idea if this is possible?
Hi ChrisFromOhio ,
If I understand correctly, the issue is that add a calculated measure in row. Please try the following methods and check if can solve your problem:
1.Drag the Attribute field to the Rows and drag the Date field to the Columns.
2.Change the Rows subtotal label.
3.Create a measure to calculate the margin.
Measure = VAR _cost = CALCULATE(SUM('Table'[Value]),'Table'[Attribute]="Cost $") VAR _sales = CALCULATE(SUM('Table'[Value]),'Table'[Attribute]="Sales $") return IF(ISINSCOPE('Table'[Attribute]),SUM('Table'[Value]), DIVIDE(_cost,_sales))4.Drag the measure to the Values.
5.The result is shown below.
Best Regards,
Wisdom Wu
So the Margin will only show up in the subtotal line? What if I wanted it before another attribute?
Try the following:
instead of using the Attribute column, create a measure that calculates each concept
Cost = calculate(sum(table[value]), table[attribute]= "Cost")
and so on for the others and also for the margin
Once you have created the 4 measures, you use them in the values section of the matrix.
If you want these values in rows or columns, you can customize the array to do the conversion in the Format / Values / Change values to rows panel.
