Forum Discussion

EvaHello's avatar
EvaHello
Helper I
1 year ago
Solved

Income statement - wanting to include gross margin % row (doesnt need to be calculated)

Hi all,   I have some sample data for an income statement.  However in my matrix, i cant format the % lines easily.  They dont need to be a calculation as i have it in my source data but they need ...
Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    Hi EvaHello ,
    Thanks for reaching out to the Microsoft fabric community forum.

    Create these Measures - 
    Formatted MTD Actual Measure - 
    Formatted MTD Actual =
    VAR MetricType = SELECTEDVALUE('YourTable'[Period])
    VAR Val = SELECTEDVALUE('YourTable'[MTD Actual])
    RETURN
    IF(
    MetricType = "Percentage",
    FORMAT(Val, "0%"),
    FORMAT(Val, "#,##0.0")
    )

    Formatted MTD FC Measure - 
    Formatted MTD FC =
    VAR MetricType = SELECTEDVALUE('YourTable'[Period])
    VAR Val = SELECTEDVALUE('YourTable'[MTD FC])
    RETURN
    IF(
    MetricType = "Percentage",
    FORMAT(Val, "0%"),
    FORMAT(Val, "#,##0.0")
    )

    Formatted MTD PY Measure - 
    Formatted MTD PY =
    VAR MetricType = SELECTEDVALUE('YourTable'[Period])
    VAR Val = SELECTEDVALUE('YourTable'[MTD PY])
    RETURN
    IF(
    MetricType = "Percentage",
    FORMAT(Val, "0%"),
    FORMAT(Val, "#,##0.0")
    )

    Create % Variance Column (e.g., Actual vs FC)
    % Variance Actual vs FC =
    VAR MetricType = SELECTEDVALUE('YourTable'[Period])
    VAR Actual = SELECTEDVALUE('YourTable'[MTD Actual])
    VAR FC = SELECTEDVALUE('YourTable'[MTD FC])
    RETURN
    IF(
    MetricType = "Percentage",
    FORMAT(Actual - FC, "0%"),
    FORMAT(DIVIDE(Actual - FC, FC), "0.0%")
    )

    Create a Matrix Visual
    Rows : Level 1
    Values : Add all the measures created

    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

    Best Regards,
    Sreeteja.
    Community Support Team 

v-sshirivolu's avatar
v-sshirivolu
Community Support
1 year ago

Hi EvaHello ,
Thanks for reaching out to the Microsoft fabric community forum.

Create these Measures - 
Formatted MTD Actual Measure - 
Formatted MTD Actual =
VAR MetricType = SELECTEDVALUE('YourTable'[Period])
VAR Val = SELECTEDVALUE('YourTable'[MTD Actual])
RETURN
IF(
MetricType = "Percentage",
FORMAT(Val, "0%"),
FORMAT(Val, "#,##0.0")
)

Formatted MTD FC Measure - 
Formatted MTD FC =
VAR MetricType = SELECTEDVALUE('YourTable'[Period])
VAR Val = SELECTEDVALUE('YourTable'[MTD FC])
RETURN
IF(
MetricType = "Percentage",
FORMAT(Val, "0%"),
FORMAT(Val, "#,##0.0")
)

Formatted MTD PY Measure - 
Formatted MTD PY =
VAR MetricType = SELECTEDVALUE('YourTable'[Period])
VAR Val = SELECTEDVALUE('YourTable'[MTD PY])
RETURN
IF(
MetricType = "Percentage",
FORMAT(Val, "0%"),
FORMAT(Val, "#,##0.0")
)

Create % Variance Column (e.g., Actual vs FC)
% Variance Actual vs FC =
VAR MetricType = SELECTEDVALUE('YourTable'[Period])
VAR Actual = SELECTEDVALUE('YourTable'[MTD Actual])
VAR FC = SELECTEDVALUE('YourTable'[MTD FC])
RETURN
IF(
MetricType = "Percentage",
FORMAT(Actual - FC, "0%"),
FORMAT(DIVIDE(Actual - FC, FC), "0.0%")
)

Create a Matrix Visual
Rows : Level 1
Values : Add all the measures created

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

  • EvaHello's avatar
    EvaHello
    Helper I
    1 year ago

    SR You are an absolute genius!!!  Thank you so so so much!!!

  • EvaHello's avatar
    EvaHello
    Helper I
    1 year ago

    v-sshirivolu could i ask one more thing, is there a way to format the % if negative to show as in brackets?

    • collinq's avatar
      collinq
      Super User
      1 year ago

      Hey EvaHello ,

      My response and screenshot has a different field name, but all you have to do is go to the data field and right click on it and then select "Conditional Formatting"



      Then, choose Font Color.

      Then, choose "rules":
      And select the less than zero and greater than zero.  Since you have to do the red with a start and an end just put a bunch on "9" in there like I  have in this screenshot (the greater than for red is greater than -999999).