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 

collinq's avatar
collinq
Super User
1 year ago

HI EvaHello ,

Welcome to the World of Power BI !!    I am not sure I am following the first issue - I am seeing the percentage in the column....is that in Power BI or is that in your sample?   If you are seeing that in the sample and not in Power BI, the solution is to format your field.  There are a few ways to do so but probably the easiest is to select the field on the right hand side of the report page:


And then, in the ribbon, select the format and select percentage:


In regards to your variance question - are you just needing to divide one field from the other, or are you trying to do something else when you say the word "variance"?

 

  • Aqua1's avatar
    Aqua1
    Frequent Visitor
    1 year ago

    collinq Hi!  Thank you for replying.   So in my data, i have periods along the top, metrics on the left hand side.  So I guess the question is how do I format the rows which are margin related?  I want some numbers in the column to be absolute,  some to be margin related.

     

    I haven't a clue how to write code to get margins, so ive calculated it all in my excel and imported everything across.  Please let me know if there's a better way