Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Do Calculation within SUMMARIZE

Hi, I'm new here and also in Power BI, I'm looking for a way to be able to do calculations within summarize when I create the table from another, I want to get the % of variation between two columns, I have this code and I want to place the % of variation in a separate column.

Tbl_DeparturesByColor = SUMMARIZE (

Sheet1,Sheet1[TYPE OF VARIETY],
"ActualTemp", SUMX(FILTER(Sheet1,[SEASON]="2023-2024"), [COMMERCIAL UNITS]),
"LastTemp", SUMX(FILTER(Sheet1,[SEASON]="2022-2023"), [COMMERCIAL UNITS])
)
)
With this I bring the two values in two columns, so far good, but I need to do the next operation and then insert it in a new column
DifSum = ActualTemp - LastTemp
DifSum / LastTemp => This result should go in a third column
Do you understand?

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Syndicate_Admin , You have create measures

     

    Create a separate table with distinct SEASON

     

    Period = distinct(Sheet1[SEASON])

     

    Join back with SEASON

     

    In period table add a Rank column

     

    Period Rank = RANKX(all(Period),Period[SEASON],,ASC,Dense)

     

    Now Create a new measure


    This Period = CALCULATE(sum('Sheet1'[COMMERCIAL UNITS]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))


    Last Period = CALCULATE(sum('Sheet1'[COMMERCIAL UNITS]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

     

    Same as week on week

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    Power BI Custom Period Till Date (PTD)- https://youtu.be/rQ3Z_LtxwQM