Forum Discussion

ChrisFromOhio's avatar
ChrisFromOhio
Helper I
2 years ago
Solved

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

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
ChrisFromOhio
Helper I
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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