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

erhang's avatar
erhang
Helper II
8 years ago

İs there anybody , who can help me pls ?

 

Thanks 

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