Calculated Measure in Row

[ChrisFromOhio]

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!

[Syndicate_Admin]

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.

[ChrisFromOhio]

Any idea if this is possible?

[Anonymous]

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

[ChrisFromOhio]

So the Margin will only show up in the subtotal line? What if I wanted it before another attribute?

[Anonymous · Accepted solution]

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

[Anonymous]

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

[ChrisFromOhio]

Hi Wisdom - my question is how do I get the fourth attribute Margin (Cost/Sales) into the rows of the matrix?