%GT Calculation?

[andrewb95]

Please see the matrix I have. 

I have a value of 1 per row and the % of grand total, I have the same for 2. 

I want to create a calulation to show the percentage when I perform (=%GT 2 - %GT 1). 

I have created these grand totals via simply right clicking onto the 1 or 2 value and selecting show value as. 

For additional information the initial column I have blanked out is just a unique identifier for each total. 

I appreciate any tips, many thanks! 

[v-yetao1-msft · Accepted solution]

Hi @andrewb95 

I created a sample, and all the values in the matrix are measures. But their formula is different from what you said, but the nature is the same.

My original data :

(1) Create a measure to group and sum column 1 and column 2.

Measure 1 = CALCULATE(SUM(MyTable[1]),ALLEXCEPT(MyTable,MyTable[ID]))

Measure 2 = CALCULATE(SUM(MyTable[2]),ALLEXCEPT(MyTable,MyTable[ID]))

(2) Calculate the percentage of each row of data to the total data .

%GT1 = DIVIDE([Measure 1],CALCULATE(SUM('MyTable'[1]), ALLSELECTED('MyTable'[ID])))

%GT2 = DIVIDE([Measure 2],CALCULATE(SUM('MyTable'[2]), ALLSELECTED('MyTable'[ID])))

(3) Calculate the difference between two percentages

diff = [%GT2]- [%GT1]

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Wimverh]

Create a measure for the %GT1 & %GT2

%GT1 = 
DIVIDE(sum(MyTable[1]),
CALCULATE(SUM('MyTable'[1]), ALLSELECTED('MyTable'[ID])))

%GT2 = 
DIVIDE(sum(MyTable[2]),
CALCULATE(SUM('MyTable'[2]), ALLSELECTED('MyTable'[ID])))

myresult = [%GT2]- [%GT1]

this will give the following result

power bi file: https://dataploration.be/forumpost/1968158.pbix 

[andrewb95]

Hi @Wimverh I am not too sure this calculation will work as I didn't mention all the items I have in my table are already measures.

1 and 2 are calculated in the following way:

1 = COUNTAX(FILTER('Table1',[FieldX]="ValueX"),[FieldY])