Forum Discussion

achanikya's avatar
achanikya
Helper I
3 years ago
Solved

calculation

Hi Tema    Could anyone please explain me how we convert the excel formula (highlighted in screenshot) to power BI explain me.   Thanks  chanikya A
Data Protection
General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi achanikya ,

    Assume that you have the table data as below screenshot, you can create a measure as below to get it. Please find the details in the attachment.

    Percent = 
VAR _2021 =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Year] = 2021 )
VAR _2022 =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Year] = 2022 )
VAR _percent =
    IF ( ISBLANK ( _2021 ) || _2021 = 0, BLANK (), DIVIDE ( _2022 - _2021, _2021 ) )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Year] ),
        SUM ( 'Table'[Value] ),
        _percent
    )

     

    What your raw data looks like? Could you please provide some sample data(exclude sensitive data) and your final expected result? Do you want to get the difference percentage between current year and previous year? Thank you.

    Best Regards

Dinesh_Suranga's avatar
Dinesh_Suranga
Continued Contributor
3 years ago

achanikya 

Hi

You can try following DAX.

Measure = (SUM('TableName'[2022]) - SUM('TableName'[2021])) / SUM('TableName'[2021])

If you use one mesure to both years then this DAX will not work.

Thank you.

achanikya's avatar
achanikya
Helper I
3 years ago

Hi Dinesh_Suranga 

I need a daynamic calculations pls let me know 

Thanks 

chanikya A

  • Dinesh_Suranga's avatar
    Dinesh_Suranga
    Continued Contributor
    3 years ago

    achanikya 

    Please share some sample data with actual column names.

    Thank you.

    • achanikya's avatar
      achanikya
      Helper I
      3 years ago

      pls share the emai Id 

      Thanks 

      Chanikya A

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi achanikya ,

        Assume that you have the table data as below screenshot, you can create a measure as below to get it. Please find the details in the attachment.

        Percent = 
VAR _2021 =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Year] = 2021 )
VAR _2022 =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Year] = 2022 )
VAR _percent =
    IF ( ISBLANK ( _2021 ) || _2021 = 0, BLANK (), DIVIDE ( _2022 - _2021, _2021 ) )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Year] ),
        SUM ( 'Table'[Value] ),
        _percent
    )

         

        What your raw data looks like? Could you please provide some sample data(exclude sensitive data) and your final expected result? Do you want to get the difference percentage between current year and previous year? Thank you.

        Best Regards