Forum Discussion

Stuznet's avatar
Stuznet
Icon for Helper V rankHelper V
8 years ago
Solved

Divide With 2 filters Measure

 

On an Excel I created a matrix for Late and On-Time is based on Status 2 Column. I want to know how to divide column "=C/D" with a Status text filter "Late" and "On-Time"? Column E is what I'm looking for is the percentage. 

 

I created a measure 

Measure= DIVIDE(SUM(Data[ID]), SUM(Data[Status2]), FILTER(Data,Data[Status2] = "Late" && Data[Status2]="On-Time"))

 

but I'm getting an error 

 

Then I tried this formula 

Measure2 = DIVIDE(CALCULATE(SUM(Data[ID]),Data[Status2]="Late"),CALCULATE(SUM(Data[ID]),Data[Status2] = "On-Time"))

 

I got this error

 

What am I doing wrong here?

Need Help
Show and Tell
Tutorial Requests
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Stuznet,

     

    You second measure is correct however you need to do a count your ID column is type text and you cannot sum text fields.

     

    Measure2 =
DIVIDE (
    CALCULATE ( COUNT ( Data[ID] ), Data[Status2] = "Late" ),
    CALCULATE ( COUNT ( Data[ID] ), Data[Status2] = "On-Time" )
)

    Regards,

    MFelix

     

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi Stuznet,

     

    You second measure is correct however you need to do a count your ID column is type text and you cannot sum text fields.

     

    Measure2 =
DIVIDE (
    CALCULATE ( COUNT ( Data[ID] ), Data[Status2] = "Late" ),
    CALCULATE ( COUNT ( Data[ID] ), Data[Status2] = "On-Time" )
)

    Regards,

    MFelix

     

    • Stuznet's avatar
      Stuznet
      Icon for Helper V rankHelper V
      8 years ago

      Thank you so much it worked I just need to change the whole number to Percentage. :) 

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

        Select the measure and on the modeling tab select the %.

         

        Regards,

        MFelix