Forum Discussion

andrewb95's avatar
andrewb95
Helper II
5 years ago
Solved

%GT Calculation?

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 - %...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Wimverh
Resolver IV
5 years ago

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's avatar
    andrewb95
    Helper II
    5 years ago

    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])