Conditional and selective sum with filters
Hey guys! how you doing? Just got an insight in my dax code, i've been working on the logic of it for a while and i think im getting somewhere, but still need help. I need the code to return the "out" and "available" status correctly for each order, in a way that it will always start with the earliest order then to the oldest. the reult i got for this actual data is right, but it changes when i change the values, getting wrong results, here is an example of the right result i got.: I have used many calculations and contitions to achieving this result, but the logic is still not complete. look at the result i get when change the values: The correct results for this last one would be available, out, available, out, out, available, out, out. The measure i am using is the "FinalCorrectStatus" you can find it in the file: DistributeStok The code is: FinalCorrectStatus = VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto]) VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido]) VAR StockAvailable = CALCULATE( SUM('Table Estoq'[Estoque Real]), 'Table Estoq'[Produto] = CurrentItem ) //soma normal soma sem condição cumulativamente VAR SomaNormal = SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ), 'DB_ORDERS'[Quant. Falta] ) //Filtro de apoio para a SomaMaiorEstoq VAR SomaMaiorApoio = FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ) //soma apenas quando o valor da "SomaNormal" for maior que o estoque VAR SomaMaiorEstoq = SUMX( ADDCOLUMNS( SomaMaiorApoio, "@Acumulado", SUMX( FILTER( SomaMaiorApoio, 'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido]) ), 'DB_ORDERS'[Quant. Falta] ) ), VAR TotalAcumulado = [@Acumulado] RETURN IF(TotalAcumulado > StockAvailable, 'DB_ORDERS'[Quant. Falta], 0) ) //diferenca entre A soma cumulativa normal e a soma apenas dos maiores que o estoque VAR Diferenca = SomaNormal - SomaMaiorEstoq //valor resultante de "Diferenca" + a quantidade do item no pedido VAR Dif_mais_QTFALT = Diferenca + SELECTEDVALUE('DB_ORDERS'[Quant. Falta]) //verifica se o estoque já atingiu seu limite, vai somando o que estiver dentro da condição de caber no estoque VAR SomaFinal = SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem && ( SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ), 'DB_ORDERS'[Quant. Falta] ) <= StockAvailable || (Diferenca + 'DB_ORDERS'[Quant. Falta]) <= StockAvailable ) ), 'DB_ORDERS'[Quant. Falta] ) RETURN IF( ISBLANK(StockAvailable), "out", IF( SomaFinal <= StockAvailable && (Dif_mais_QTFALT <= StockAvailable), "available", "out" ) ) its very complex, and i am almost giving up.. Can anyone help me please? ThankyouComplex conditional summing
Hey everyone! how you doing? I have been now a month trying to make this logic work in dax but no solution. Cna anyone please help me with this? or even just say that power Bi will not be able of doing it happen? Here is the thing: I need to distribute evenly the stock of an item throught the orders of this item, in a way that the result is as shown in the table bellow: I need the stock to be distributed in a way that when its not enought to the order it will try to fit in the other order until the total stock is used. Use the stock amount of 21 for this example, you will see that the ones set as available, when summed will be exactly 21. The status column is the outcome that i need from the dax code. This is very hard, i don't even know if its possible; can anyone please helpe me with it? Thankyou a lot allready.
How to Sum and accurately summarize when conditions are in place
Hello Everyone, I am trying to have a Sum calculation based on several columns per row, there is an identifier that indicates that 3 columns should be included in the sum, and for any others only 2 columns. The issue I am having is that the row level calculation is correct and works perfectly, but when grouping by customers the identifier gets ignored, and the total sum aggregates the 2-column formula when in reality there is an internal item that has the "True" identifier. The second row on the example above shows the calculation when the identifier is "True", and yet the total amount does not show the actual sum of the 2 rows that are part of the customer revenue total. Perhaps using summarize will solve the issue but I don't understand how the syntax will work on my case. I have tried different measures and all end up the same, see them below Revenue = IF( SELECTEDVALUE('Carriers'[FRS_Product]) = TRUE, CALCULATE( SUM('Master Accounts Bound'[Renewal Net House Commission]) + SUM('Master Accounts Bound'[Agency Fees.amount]) + SUM('Master Accounts Bound'[Policy Fee]) ) , CALCULATE( SUM('Master Accounts Bound'[Renewal Net House Commission]) + SUM('Master Accounts Bound'[Agency Fees.amount]) ) ) --------------------------------------------------------------------------------------------------------------------------- Revenue X = SUMX('Master Accounts Bound', IF( SELECTEDVALUE('Carriers'[FRS_Product]) = TRUE, 'Master Accounts Bound'[Renewal Net House Commission] + 'Master Accounts Bound'[Agency Fees.amount] + 'Master Accounts Bound'[Policy Fee] , 'Master Accounts Bound'[Renewal Net House Commission] + 'Master Accounts Bound'[Agency Fees.amount] ) ) -------------------------------------------------------------------------------------------------------------------
measure is not showing total value in table visual
hi everyone i have created a measure that calculates sales agents commission based on his achievment % for each sales category. when i added the measure to a table visual, it's showing the the total value of the table visual as 0 instead of summing the commission values for each category. the total for measure 'new commission calculation' shoulde be 660 instead of 0 and the total for measure 'new agent incentive' should be 225 instead of 0
sum total except for repeating rows
Hello community, I need a measure that sums follower_count and following_count (VALUE Column). I use some simple measures for this. However, this calculation should not calculate the repeating rows in the userid column. for example, I'm using this: User Reached = Sum(Single[follower_count]) + SUM(Single[following_count]) Thanks,
