Forum Discussion

tiagolangendorf's avatar
tiagolangendorf
Regular Visitor
2 years ago
Solved

Formula dax

Olá! Preciso de ajuda com o Power BI. Tenho duas tabelas: uma chamada 'Ordem de Compra' e outra 'Pedido'. Na tabela 'Pedido', tenho as colunas 'Índice' (variando de 1 ao infinito), 'Pedido' (contendo o número do pedido), 'Item' (com o código do produto) e 'Qtd' (indicando a quantidade do pedido). Na tabela 'Ordem de compra', tenho as colunas 'Índice', 'PO' (número da ordem de compra), 'Item' (código do item) e 'Qtd comprado' (quantidade da ordem de compra).

Como revendedor, compro produtos para revenda, então meus pedidos estão diretamente ligados aos meus pedidos de compra. Preciso de uma fórmula DAX que indique qual ordem de compra atenderá a um pedido específico. Por exemplo, na tabela abaixo, tenho o pedido 1212 solicitando 15 unidades, para que possa ser atendido pelo PO 5025, restando 55 unidades para atender o próximo na fila, que é o pedido 1213. Depois de cumprido isso, restariam 35 unidades para cumprir parcialmente o pedido 1214. Vou ilustrar na tabela abaixo como ficaria se a fórmula funcionasse:

Índice   Ordem   Item     Qty   PO

1           1212      PA123  15    5025

2           1213      PA123  20    5025

3           1214      PA123  50    5025,5026

4           1215      PA123  45    5026

5           1216      PA123  35    5026

6           1217      PA123  80    5026,5027

Aqui estão as tabelas:

 

Ordem:

 

Índice Item de ordem Qtde

1 1212 PA123 15

2 1213 PA123 20

3 1214 PA123 50

4 1215 PA123 45

5 1216 PA123 35

6 1217 PA123 80

 

Ordem de compra:

 

Índice PO Item Qtde

1 5025 PA123 70

2 5026 PA123 90

3 5027 PA123 150

  • JamesFR06's avatar
    JamesFR06
    2 years ago

    Hi @tiagolangerdof,

     

    You just made a Mix when you copy the measure

    Change this with ==> 

    Min( 'Ordem de compra'[indice] ),

     

     

9 Replies

  • JamesFR06's avatar
    JamesFR06
    Resolver IV
    2 years ago

    Hi tiagolangendorf 

     

    I made this hope that will help you.

     

     

    MEASURE =
    VAR ValidIndex =
    SELECTEDVALUE ( Cust_Orders[Indice] )
    VAR Salescumul =
    CALCULATE (
    SUM ( Cust_Orders[Qte] ),
    Cust_Orders[Indice] <= ValidIndex,
    ALL ( Cust_Orders ),
    ALLEXCEPT ( Cust_Orders, Cust_Orders[Item] )
    )
    VAR ValuePreviousSalesCumul =
    CALCULATE (
    SUM ( Cust_Orders[Qte] ),
    Cust_Orders[Indice] <= ValidIndex - 1,
    ALL ( Cust_Orders ),
    ALLEXCEPT ( Cust_Orders, Cust_Orders[Item] )
    )
    VAR Dernierindexcumulachat =
    CALCULATE (
    MIN ( Achats[Indice] ),
    Salescumul <= Achats[CUmul],
    REMOVEFILTERS ()
    )
    VAR Premierindexcumulachat =
    CALCULATE (
    MIN ( Achats[Indice] ),
    ValuePreviousSalesCumul <= Achats[CUmul],
    REMOVEFILTERS ()
    )
    VAR div =
    CALCULATE (
    MIN ( Achats[Indice] ),
    ValuePreviousSalesCumul <= Achats[CUmul],
    REMOVEFILTERS ()
    )
    VAR result =
    CALCULATE (
    CONCATENATEX (
    FILTER (
    Achats,
    Achats[Indice] IN { Premierindexcumulachat, Dernierindexcumulachat }
    ),
    Achats[PO],
    ", "
    ),
    REMOVEFILTERS ()
    )
    RETURN
    result

     

    Cumul is a calculated clumn in the Table Achats (Purchase Orders)

    CUmul =
    var ActivIndex=Achats[Indice]
    return
    calculate(sum(Achats[Qte]),Achats[Indice]>=1&&Achats[Indice]<=ActivIndex,all(Achats),ALLEXCEPT(Achats,Achats[Item]))
    • tiagolangendorf's avatar
      tiagolangendorf
      Regular Visitor
      2 years ago

      Thank you very much!! I'm seeing a light!! But here the result was partial, see below: a question, why when I use a calculated column the result is different from a measure? Below, the first image is of the measure and the second is of the calculated column:This is a measure that didn't yield the correct calculation.This is a calculated column.

    • tiagolangendorf's avatar
      tiagolangendorf
      Regular Visitor
      2 years ago

      Hello I couldn't do it as you did, is there any other formula that you didn't show?

      • tiagolangendorf's avatar
        tiagolangendorf
        Regular Visitor
        2 years ago

        My result was not the same, can anyone help me? in our friend JamesFR06's formula it seems that the accumulated works in my formula it doesn't. Here is a link to my archive

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Do you want a static assignment, or can any of this be impacted by user filters or changes in sort order?

    • tiagolangendorf's avatar
      tiagolangendorf
      Regular Visitor
      2 years ago


      Hello, I have filters only for zero balance, for example. I have a formula that first shows where in the queue that request is and then uses the inventory balance to deduct from the request. I use this to get orders with zero balance because those are the ones I intend to use the formula I'm looking for... So yes, there is a filter to only show items with zero balance.

  • EricoVincentciu's avatar
    EricoVincentciu
    Helper II
    2 years ago

    hai i got error with my dax calculated formula 

     

    i wana make some tabel using dax like this : 

     

     

     

    actually i already make it at power bi desktop like this : 

     

     

     

    but when i publish to power bi fabric the data has been change like this : 

     



    i feel

    this is because of my dax calculate error 

     

     



    this is my dax code : 

    Januari = IF(
        AND(
            'Tabel Fuel'[Month] = 1,  // Check if month is 1
            'Tabel Fuel'[NewDate].[Month] = "January"  // Check if month name is January
        ),
        'Tabel Fuel'[QTY 2],  // Return QTY 2 if conditions are met
        0  // Return 0 as the default value if conditions are not met
    )
     
    i already make dimention date table but i have struggle to calculate Tabel fuel to DIMDATE tabel 
     

     

     

     

    could u please help me ??

    thank you