Forum Discussion

Premlatapandey9's avatar
Premlatapandey9
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

How to get the Percentage based on Column sum from two different tables

Hello Everyone,

 

I want to calculate the percentage based on two tables can you please help my data set looks like below 

 

Table 1 Table 2   
Duplicate count No Duplicate count 
2 1  
3 2  
4 3  
5 4  
6 5  
     
Total T120Total T215 
     
Duplicate issues%Total T1/TotalT2+TotalT120/20+1557.12
No Dupe issue%Total T2/TotalT2+TotalT115/20+1542.85
     

 

Thanks and Regards

@members

Need Help
Tips and Tricks

2 Replies

  • collinq's avatar
    collinq
    Icon for Super User rankSuper User
    3 years ago

    Hi Premlatapandey9 ,

     

    In the future, for DAX like this it would be better to use that forum rather than the Service forum.  That said - the formula would be this:

    Measure = Calculate(Divide(sum(Table2[NoDup]),(sum(Table1[duplicate]))))
    To get the .75
    And, to get the 1.33 you would flip that to this:
    Measure = Calculate(Divide(sum(Table`[duplicate]),(sum(Table2[NoDup]))))
     
    But, it appears by your example that you want the percentage of each table against each other with the summation of each column.  An easy (althought not elegant) solution is:
     
    1. Table1 summation:
    Table1Sum = Sum(Table1[duplicate])
     
    2. Table2 summation:
    Table2Sum=Sum(Table2[NoDup])
     
    3. Combination:
    TotalofT1andT2 = ((Table1[Table1Sum]+(Table2[Table2Sum])))
     
    4. Dividing results:
    Table1Percentage = Table1[Table1Sum]/Table1[TotalofT1andT2]   (is 57)
    Table2Percentage = Table2[Table2Sum]/Table1[TotalofT1andT2]   (is 43)
     
     
     
  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    Hi Premlatapandey9 ,

     

    Are you looking for something like this?

     

    Here the code in DAX:

    MeasurePerc1 = 
DIVIDE ( 
    SUM ( Table1[Duplicate count] ),
    SUM ( Table1[Duplicate count] ) + SUM ( Table2[Duplicate count] )
) * 100

    MeasurePerc2 = 
DIVIDE ( 
    SUM ( Table2[Duplicate count] ),
    SUM ( Table1[Duplicate count] ) + SUM ( Table2[Duplicate count] )
) * 100

     

    Let me know if this solves your query 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/