Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate a column from two calculated column in a report table

Hi dear community !   I have a table on my report where i put the number of cars at date 1, the % of the total1 (by adding the same column and put it as "percent of a grand total"), number of cars ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    % total_2 =
CALCULATE ( SUM ( 'Table'[number] ), ALLEXCEPT ( 'Table', 'Table'[date] ) )
    / CALCULATE ( SUM ( 'Table'[number] ), ALL ( 'Table' ) )

    Change the measure as below

    % total_2 =
SUMX(ALLEXCEPT ( 'Table', 'Table'[date] ),'Table'[number])
    / SUMX(  ALL ( 'Table' ) ,'Table'[number])

     

    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.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous 

Assume you have a table as below

% of total2 = (numbers of cars for each date)/(numbers of cars for all dates)

% of total1= (numbers of cars for each previous date)/(numbers of cars for all dates)

 

Something as below:

 

You could create measures

% total_2 =
CALCULATE ( SUM ( 'Table'[number] ), ALLEXCEPT ( 'Table', 'Table'[date] ) )
    / CALCULATE ( SUM ( 'Table'[number] ), ALL ( 'Table' ) )

% total_1 = CALCULATE([% total_2],FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])-1))

Measure = ([% total_2]-[% total_1])/[% total_2]

 

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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Maggie,

 

I think we are close to the solution. I have only a little issue with you method, i cant call Table'number' because in my case it's a measure. So i can't call a measure in the funciton SUM. 

 

I'll try to find a way !

 

If you have any suggestion, i'm open.

 

Thank you :)

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

    Hi Anonymous 

    % total_2 =
CALCULATE ( SUM ( 'Table'[number] ), ALLEXCEPT ( 'Table', 'Table'[date] ) )
    / CALCULATE ( SUM ( 'Table'[number] ), ALL ( 'Table' ) )

    Change the measure as below

    % total_2 =
SUMX(ALLEXCEPT ( 'Table', 'Table'[date] ),'Table'[number])
    / SUMX(  ALL ( 'Table' ) ,'Table'[number])

     

    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.