Forum Discussion

Justair07's avatar
Justair07
Icon for Resolver I rankResolver I
7 years ago
Solved

Divide Sum of Values in Visual Table

Hi,

 

This one really has me stumped :(

 

I have two valuse I need to divide (RunHrs/DownHrs). Both valuse are sums from my data source. I tried something silly before I realized why it wouldn't work. I tried to add a custom column dividing the numbers in the query table. But obviously this won't work because the two valuse are sums and there is never a RunHrs and DownHrs on the same record so it's always divided by 0 or 0 is divied by a number.

 

This doesn't have to be displayed in a table, I figured it would be easier for me to display in a table.

 

Any help is much appreciated.

 

Thank you

 

 

 

Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Justair07,

     

    You are adding a column to your data table, you need to add a measure this will then be calculated based on context and return the value you need.

     

     

    Regards,

    MFelix

     

6 Replies

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

    Hi Justair07,

     

    You just need to had the following measure to your model:

    RunPerc = SUM(Table[RunHrs]) / SUM(Table[DownHrs])

    This is based that your table has two different columns on for Run and another for Down.

     

    If your table as a classification to make the difference between RunHrs and DownHrs you need to have something like this:

    RunPerc = CALCULATE(SUM(Table[Hours]); Table[HoursType] = "RunHrs")/CALCULATE(SUM(Table[Hours]); Table[HoursType] = "DownHrs")

    Change the name of the columns with the ones from your table.

     

    If any of this examples doesn't fit your model please share the setup of your table to calculate the sum of hours.

     

    Regards,

    MFelix

     

     

     

    • Justair07's avatar
      Justair07
      Icon for Resolver I rankResolver I
      7 years ago

      MFelix

       

      Thank you for the reply.  Your 2nd solution is more applicable, however I'm getting an error after each semi colon (see 1st image).

       

      Just to take another shot, I created two custom columns, one for DownHrs and one for RunHrs then used your first solution. But the math isn't quite right (see 2nd image).

       

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago
        Replace the semicolon by a comma it has to do with regional settings my dax separstor is semicoloon.

        Regards,
        MFelix