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

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

erhang's avatar
erhang
Helper II
8 years ago

dear  v-juanli-msft ;

 

thanks for your helps 

 

Erhan 

  • erhang's avatar
    erhang
    Helper II
    8 years ago

    dear v-juanli-msft ;

     

    is it possible to send me pbix file of these formula , because when i try to use it on power bi some formulas are not working :(

    • 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