Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Row context measure

Hi,

 

I would be extremely thankful if someone could help me on the following:

My data consists of a hierarchy of business units and data on  turnover, acquisition turnover, prior year turnover and deflated turnver. I have created a measure (new column), which calculates the sales growth as follows (This is for the lowest level of business unit):

 

IFERROR((measure1[Turnover]+measure1[Acquisition Turnover])/(measure1[Prior Year Turnover] + measure1[Deflated Turnover])-1, 0)
 
When I drill up to the aggregate level of business unit, I want to be able to aggregate - sum the turnover, acquisition turnover, prior year turnover and deflated turnover, but the new column should be calculated at row level with the same calculation as the above step and should not be summed or averaged.
I am new to PowerBI and tried using the custom column in query but that shows the same values throughout the different drill levels and the new column under measure sums this value for all the drill levels. I read something about row context under measures but am not able to understand it completely.
 
Any help will be greatly appreciated.
 
Best regards,
Yeshika
 
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    You need to create measures instead column in query editor or new column in Modeling menu.

    Measure = SUM(Table1[turnover])+SUM(Table1[acquisition turnover])+SUM(Table1[prior year turnover])+SUM(Table1[deflated turnver])

Measure 2 = (SUM(Table1[turnover])+SUM(Table1[acquisition turnover]))/(SUM(Table1[prior year turnover])+SUM(Table1[deflated turnver]))

     

    For example, add them in a matrix visual,

    click on the icon "expand all down one level in a hierarchy"

    or click on the icon "go to next level in a hierarchy"

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    You need to create measures instead column in query editor or new column in Modeling menu.

    Measure = SUM(Table1[turnover])+SUM(Table1[acquisition turnover])+SUM(Table1[prior year turnover])+SUM(Table1[deflated turnver])

Measure 2 = (SUM(Table1[turnover])+SUM(Table1[acquisition turnover]))/(SUM(Table1[prior year turnover])+SUM(Table1[deflated turnver]))

     

    For example, add them in a matrix visual,

    click on the icon "expand all down one level in a hierarchy"

    or click on the icon "go to next level in a hierarchy"

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.