Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

 

I need to divide each row, banana/apple, get a result and sum it all up. The problem is that Power BI sums the collumn and then divide, which gives me a non-real result.

How can I solve this?

 

Cheers!

12 REPLIES 12
flath
Helper II
Helper II

Hi, @MarianaBotelho !

I believe that you'll need to transform your data table from this

flath_4-1655817740160.png

 

into this:

flath_0-1655815437242.png


Then, you'll have to create a measure:

Ratio = SUM( Table[Quantity] )
     

 

After this, you'll add a 'Stacked Column Chart' with the following data fields setup:

Axis X:  Table[Date] (field)
Axis Y: [Ratio] (measure)
Legend: Table[Produtc] (field)

Doing so, you'll have something like this:

flath_2-1655816672060.png

 

One way to solve the issue related to presenting dates without products is to change the data structure of the Table[Date] field. For this, do the following:

flath_3-1655817149586.png

1. Click on the field 'Date',

2. Then, change the data structure format from 'Date' to 'Text'

 

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. 

 

Regards,

 

 



Hi @flath I am still new to the Power BI. Could you please share workflow how to transform the columns as above? I have just working hours of the sensor and whole time when it could work. Lets say my sensor work for 10 hours from day and I need to make a percentage calculation for a day or week( I will try to make this by date range)

Anonymous
Not applicable

If the columns are differnt the how can we achieve this(2 columns are coming into 2 different tables)

You can use PowerQuery and "Unpivot" the different fruits into one column (Attribute), creating a new column of quantity (value). The Date will double up in each row, the same for Banana & Apple. 

 

The basic of a proper table is one attribute per column (date, product, value, store, customer, ...). 

 

Depending on the complexity of your model, you'll want to use dimension tables and link that to your fact table(s). 

Anonymous
Not applicable

@MarianaBotelho

 

You can also write a measure such as the following:

 

Apple to Banana Ratio:= SUMX (
    'TableName',
    DIVIDE('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. Go To bipatterns.com for more techniques and user guides.

 

Thanks,

Ryan Durkin

@MarianaBotelho - could you perhaps show the result that you want.

 

I have created this small sample on your data and can get some different results:

Skærmbillede 2016-06-15 kl. 20.58.42.png

 

Ratio is calculated as a measure with this formula: (I am still trying to understand why the total for this is 200)

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

RatioColumn is calculated as a column in the table with this formula: (from what you write I think this is what you want)

RatioColumn = DIVIDE( Table1[Banana], Table1[Apple] )

 

/sdjensen

Basically I would always advise you to solve your calculations with measures if posible and you can get Ratio to return the same total as the calculated column if you make a small change to the DAX formula.

 

Here is the formula that will return the same as the calculated column.

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

Skærmbillede 2016-06-15 kl. 21.15.16.png

/sdjensen
Anonymous
Not applicable

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

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.

@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))

Capture384.PNG

 

 

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,

 

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:

Table Example.png

 

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:

 

Grafico Example.png

 Did I made myself understandeable this time?

 

Cheers!

@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?

/sdjensen

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.