Forum Discussion

erhang's avatar
erhang
Helper II
8 years ago
Solved

Dax formula Help

Hi dear Power BI Family ;   İ need your helps one more time to create a formula about below situation .   İ have two tables as below  .   Table 1 : contains  documents number , product , reques...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi erhang

    I can get this result with the help of formula below

    stock qty =
RELATED ( Sheet1[stock qty] )
    rank =
VAR rank1 =
    RANKX ( ALLEXCEPT ( Sheet2, Sheet2[product] ), [requested delivery day],, ASC )
VAR rankcondition =
    IF (
        CALCULATE (
            COUNT ( Sheet2[document number] ),
            FILTER (
                ALLEXCEPT ( Sheet2, Sheet2[product] ),
                [requested delivery day] = EARLIER ( Sheet2[requested delivery day] )
            )
        )
            >= 2,
        RANKX (
            FILTER (
                ALLEXCEPT ( Sheet2, Sheet2[product] ),
                [requested delivery day] = EARLIER ( Sheet2[requested delivery day] )
            ),
            [document number],
            ,
            ASC
        )
    )
RETURN
    IF ( rankcondition = 2, rank1 + 1, rank1 )
    residue =
VAR cumulativesum =
    CALCULATE (
        SUM ( Sheet2[requested qty] ),
        FILTER ( Sheet2, [rank] <= EARLIER ( [rank] ) )
    )
RETURN
    [stock qty] - cumulativesum
    flag =
IF (
    [residue] >= 0,
    1,
    IF ( [residue] < 0 && [residue] > MIN ( [residue] ), 0, -1 )
)
    distrubutedstocks =
VAR finalresidue =
    CALCULATE ( MIN ( [residue] ), FILTER ( Sheet2, [flag] = 1 ) )
RETURN
    IF ( [flag] = 1, [requested qty], IF ( [flag] = 0, finalresidue, 0 ) )

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

Hi erhang

Here is my pbix.

Sheet1 show every distinct step, Sheet2 show the steps calculated by the formula i provided before.

 

Best Regards

Maggie

 

erhang's avatar
erhang
Helper II
8 years ago

dear v-juanli-msft ;

 

Thanks for your reply and help 

 

İ am mixed a little bit , in your pbix file there are 2 tables ( sheet1 and sheet2) do i have to use both of them ?

 

İ just opened a new file and tried to use your formulas , also just i copied from your file and i got below message on "rank column" 

i did not create any measure or formula on sheet1 , just i thought that with the formulas in sheet2 i can  calculate what i wanted .

 

so do i need to calculate as your sample pbix file or why did i take this error ? con you give me some advice pls ?

 

thank you very much.

 

Erhan 

 

 

  • erhang's avatar
    erhang
    Helper II
    8 years ago

    dear v-juanli-msft ;

     

    i created your " every distinct step" explanation and "distrubuted stock " worked  but i noticed that when i added more product and different product calculating become wrongly.you can see as in below picture .

     

    infact i just want the final situation as below , we are closer to final for just only one product type it is ok but when i add the second product type to the lines (in column) system is nat calculating , i hope it is clear and we can do it , thanks for your supports dear friend 

     

     

     

     

     

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      8 years ago

      Hello erhang

      Sorry for replying so late.

      After reviewing and testing, I modify my original pbix to a clear one which takes two different products into consideration.

      Here is my modified pbix.

       

      Best regards

      Maggie