Forum Discussion

bhuprakashs's avatar
bhuprakashs
Helper I
8 months ago
Solved

Need column level difference

Hi team,   I need a column level difference to show in table metrix.  current data: Column level I am using a Category from a table in table metrix. how to show difference between colum...
Need Help
  • MFelix's avatar
    MFelix
    8 months ago

    Hi bhuprakashs ,

     

    If you are using a live connection this is a good option to use visual calculations:

     

    Versus previous = [Total Value] - PREVIOUS([Total Value],COLUMNS)

     

    The total value is your measure:

     

     

    If you don't want to show difference when the value is blank on the last column just redo to:

    Versus previous = IF(ISBLANK([Total Value]), BLANK(), [Total Value] - PREVIOUS([Total Value],COLUMNS))

     

     

    Check the detail how to use visual calculations:

    https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview

     

rohit1991's avatar
rohit1991
Super User
8 months ago

Hii bhuprakashs 

 

Since Economy and Supreme should show no change, and the other columns should subtract the previous one, use this pattern:

Column Difference =
VAR Col = SELECTEDVALUE('Category'[ColumnName])
VAR PrevCol =
    SWITCH(
        Col,
        "Basic",    "Economy",
        "Deluxe",   "Basic",
        "Premium",  "Deluxe",
        BLANK()       
    )
RETURN
IF(
    ISBLANK(PrevCol),
    BLANK(),
    CALCULATE([YourValueMeasure])
        - CALCULATE([YourValueMeasure], 'Category'[ColumnName] = PrevCol)
)