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 columns like

Economy no change, Basic - Economy , Deluxe - Basic , Premium - Deluxe , Supreme no change

Please help me. Also I am using data source as Live connection so not able to add new table or column so please help a solution of measure only if possible. thanks


Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    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)
)