Forum Discussion

MTTsoftwareuser's avatar
MTTsoftwareuser
Frequent Visitor
3 years ago

Summing Data from One Column Based on Text in Other Column

Hi All,

 

I have three columns in a table that I am considering - Hours, Has WO, and Multiple WO.  I'm trying to create a measure that will calculate the Hours for records where Has WO = Yes AND Multiple WO = Multiple and then divide that by the Hours for records where Has WO = Yes.  In the attached pic, the calculations would wind up being 35.5 / 120.

 

 

 

I'm using this as my measure:

 

Chart 4b = DIVIDE(CALCULATE(SUM ('Data'[Hours]),'Data'[Has WO] = "YES"),CALCULATE(SUM ('Data'[Hours]),'Data'[Has WO] = "YES",'Data'[Multiple WO] = "MULTIPLE"))
 
But my result in PBI is this
I know it's not working because the results aren't percentages and aren't converting to percentages - something tells me it's doing some summing but not division.  
 
Thanks in advance!
 
Gregg

 

Need Help

6 Replies

  • DOLEARY85's avatar
    DOLEARY85
    Resident Rockstar
    3 years ago

    Hi,

     

    based on your description and then measure -  it looks like measure is dividing the total by the amount rather than how it was described above.

     

    i've tried this and it'w working:

     

    Measure = DIVIDE(CALCULATE(SUM('Table (2)'[Hours]),'Table (2)'[Has WO]="Yes",'Table (2)'[Multiple WO]="Multiple"),CALCULATE(SUM('Table (2)'[Hours]),'Table (2)'[Has WO]="Yes"))
     
    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍