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 calculation required for the same.

 

 

 

 

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

     

6 Replies

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

    Hi yashdsin ,

    It seems that you want to calculate the difference between the two values.

    Normally, we could achieve that with three ways. You could choose the way based on your requirement.

    1. Create the custom column in query editor ( columnA - column B)

    2. Create the calculated column with dax ( columnA - column B)

    3. Create the measure with dax.

    If you still need help, please share your data sample and your desired output so that we could help further on it.

    Best Regards,

    Cherry

     

    • yashdsin's avatar
      yashdsin
      Frequent Visitor
      7 years ago
      How can I attach a excel file or a pbix file in a question or reply section?
      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi yashdsin ,

        Sorry for the delay.

        You could upload your data sample or pbix in OneDrive and post the link here. Do mask sensitive data before uploading.

        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