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 - %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! 

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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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])