Forum Discussion

MarianaBotelho's avatar
MarianaBotelho
Microsoft Employee
10 years ago

Divide row by row

Hi guys, I've been having a little problem doing some math. I have the following table Time            Banana  Apple 1300-1305    1000      50 1305-1310    2000      30 1310-1315    3000      10...
Need Help
Anonymous's avatar
Anonymous
Not applicable
10 years ago

Hi MarianaBotelho,

 

You can do a calculated column.  Banana to Apple Ratio :='TableName'[Banana]/'TableName'[Apple]

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

 

Thanks,

Ryan Durkin

MarianaBotelho's avatar
MarianaBotelho
Microsoft Employee
10 years ago

Hi, so I still have one problem.

I apologize if I'm not being able to explain myself well enought.

My table uses slices of five minutes. When I need a one hour slice, I need it to sum everything in that one hour slice for the Banana and Apple column and then divide them.

Example, let's use 10 minutes slices.

 

This is my original table

Time           Banana   Apple

1300-1305    1000       50

1305-1310    2000       30

1310-1315    3000       10

1315-1320    2500       20

 

I need something like this

Time           Banana  Apple

1300-1310    3000      80

1310-1320    5500      30

 

And then I'll do the math you suggested.

Any way to do this?

 

Cheers.

  • sdjensen's avatar
    sdjensen
    Solution Sage
    10 years ago

    MarianaBotelho - did you try my last formula?

     

    If that doesn't work I think I need some more infomation on how you change the 5 minute slicer into 10 minutes or an hours?

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    MarianaBotelho

     

    According to your description, you want to change the 5 minutes slicers into 1 hour slicers. Right?

     

    In this scenario, you can create calcualted column for getting the Hour part from each 5 minutes slicer. The DAX can be like:

     

    Hour = IF(LEFT(Table[Time],2)=left(RIGHT(Table[Time],4),2),left(RIGHT(Table[Time],4),2),LEFT(Table[Time],2))

     

     

    Then you only need to drag Hour, Banana and Apply column into table visual. It will aggregate on Hour level automatically. Then you can use measure formula as sdjensen suggested.

     

    Ratio = 
SUMX(
	Table1,
	DIVIDE( CALCULATE( SUM(Table[Banana]) ), CALCULATE( SUM(Table[Apple]) ) )
)

    Regards,

     

    • MarianaBotelho's avatar
      MarianaBotelho
      Microsoft Employee
      10 years ago

      First, I'd like to thank all the answers. Second, I'd like to appologize, because I'm not making myself understandeable. I'll try again.

       

      I have the following table:

       

      With this table, I need to generate a chart, that show two stacked (Banana and Apple) columns, the first with the sum of the data from 07/02 and the second with the sum of the data from 07/03.

      Something like this:

       

       Did I made myself understandeable this time?

       

      Cheers!