Dividing the sums of two columns after selection of certain cells

L

Legacy 468626

Guest
Hello everybody,

I have learned PowerBi so far from tutorials and youtube videos and this is the first time I am posting. There is an issue which I seem not to find a workaround. I import my raw data in power query and then edit it such that I can subtract various tables for my purposes.

One of my table is formed by filtering the raw data with two filters like>

Table3 = SELECTCOLUMNS(FILTER(RAWDATA, AND ([Filter1]="Filter1related", [Filter]="Filter2related")), "Identifier"=[RawDataColumn1], "QuantityOUT"=[RawDataColumn2], "Column3Name"=[RawDataColumn3], "Column4Name"=[RawDataColumn4])

This returns me a table with four columns and 2000 rows, where the 2nd column is the individual out quantities of my data. Up to here, everything is good. Since I need to make a calculation btw In-Out quantities, I create a new column and get the Quantity IN data from another table by:

QuantityIN=SELECTCOLUMNS(FILTER('AlreadyCreatedTable2',[Identifier]='Table3'[Identifier]), "QTY_IN", [Qty_In_inTable2])

Now, this table brings back around 1400 values under Quantity In Column. the values are also correctly displayed.

So, the number of columns I have in table 3 was initially 2000, and 1400 of these rows are now filled with QTY IN values. It is normal not to have the 600 data, due to data extraction times.

What I am aiming to achieve is to sum and divide all the values in QTY IN and QTY OUT, and then divide them to each other.

I am able to do the division=QuantityOut/QuantityIn calculation for each row separately, with the help of a command which only calculates and returns a value, only when QuantityIn is bigger than 0, and qty in is bigger than quantity out (IF + and). At the end, my table looks like this:
first four columns are from the raw data.
QuantityIn column is from another table, which was previously extracted from the raw data. I hope you will be able to see the below table easily.

IdentifierQuantityOutColumn3NameColumn4NameQuantityInDivision
166aXX8866/88
233bXXblankno calculation is done automatically (if statement)
332cDC3632/36
422dAAblankno calculation
555eBG7755/77
667fDA8967/89
Under Column 1,2,3 and 4, I have 2000 filled rows (coming from table 3 definition)
Under quantity in, 1400 filled cells (coming from qty in selection)
Accordingly, Division has also 1400 filled cells.

Now, my aim is to calculate the SUM of QuantityOut for all the cells where both QuantityOut and QuantityOUT are filled, at the end, I would like my calculation to show the following:
(66+32+55+67)/(88+36+77+89)
My aim is to place this division in percentage in a card visualization.

Would you guys please help me to solve this?

Greetings and Namaste,
Shivan
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top