Forum Discussion

yashdsin's avatar
yashdsin
Frequent Visitor
7 years ago
Solved

Creating Difference between two Values for geographical Chart

Hi, I have this data set and i want to create a third column called Difference like (3.10-2.70 = 0.4 )so that i could place it on the geographical chart. Could anyone help me with the Dax calculatio...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi yashdsin,

    If you want to calculate the difference for the product per month, you could create the calcualted columns below.

    Please note that you need to change the data type of Index column to be whole number after creating it.

    Index = RIGHT('Table'[Sales],1)

Difference =
VAR a =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
                && 'Table'[Date] = EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    IF ( a = BLANK (), BLANK (), a - 'Table'[Value] )

    Then you could create the matrix like below.

    PS: We cannot achieve the same output as that in excel.

    Hope this can make sense.

    Best  Regards,

    Cherry

     

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi yashdsin ,

Have you solved your problem?

If you have solved, could you share the solution or accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly?

If you still need help, please share your sample pbix so that we could help further on it.

Best  Regards,

Cherry

 

yashdsin's avatar
yashdsin
Frequent Visitor
7 years ago

No, Actually i need to see the diffrence between two cells Subtracting Product1 & Product2 in cell B2 & B3 i should be getting results in Cell B4 and so on for all the columns C, D, E etc but i am getting it as a whole for Product1 & 2 respectively as a total which is not required. Please help as i am still a learner.

 

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi yashdsin,

    If you want to calculate the difference for the product per month, you could create the calcualted columns below.

    Please note that you need to change the data type of Index column to be whole number after creating it.

    Index = RIGHT('Table'[Sales],1)

Difference =
VAR a =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
                && 'Table'[Date] = EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    IF ( a = BLANK (), BLANK (), a - 'Table'[Value] )

    Then you could create the matrix like below.

    PS: We cannot achieve the same output as that in excel.

    Hope this can make sense.

    Best  Regards,

    Cherry