Calculate a column from two calculated column in a report table

[Anonymous]

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 at date 2 and % of the total2  for this date (same process).

But I want to add a column to my table to compute this formula : (%of total2 - %of total1)/ %of total2.

I tried with measures, quick measures.... but the issue is that I can't work with these columns values as variables like the number of cars (which are 2 calculated measures), it's calculated only in the table.

I think the solution is not so complicated but to find it I need your help.

Thank you !

[v-juanli-msft]

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]

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 · Accepted solution]

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.