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.
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
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.
| Identifier | QuantityOut | Column3Name | Column4Name | QuantityIn | Division |
| 1 | 66 | a | XX | 88 | 66/88 |
| 2 | 33 | b | XX | blank | no calculation is done automatically (if statement) |
| 3 | 32 | c | DC | 36 | 32/36 |
| 4 | 22 | d | AA | blank | no calculation |
| 5 | 55 | e | BG | 77 | 55/77 |
| 6 | 67 | f | DA | 89 | 67/89 |
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
