Forum Discussion

Avivek's avatar
Avivek
Icon for Post Partisan rankPost Partisan
3 years ago
Solved

Need help with the dax

Hello everyone!   Recently I have created a report based on an excel which has few calculations. The calculations in the excel are as below: Sales Rep Expected Return Date Act Return Date Ac...
Need Help
Show and Tell
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Add the Request ID field to the visual and you will see why the values are "different "

Avivek's avatar
Avivek
Icon for Post Partisan rankPost Partisan
3 years ago

PaulDBrown Its same what you had shared 

% Late Returns =
IF(
    ISINSCOPE('Case Order Fact'[CREATED_NAME]),
    BLANK(),
    DIVIDE([Late return],[Correct return]+[Late return])
)
 
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
3 years ago

Can you change the last two measures in the image to the SUMX measures and post the screenshot?
Also, which table is the Sales Rep field in the visual coming from and which field are you using in the SUMX measures?

  • Avivek's avatar
    Avivek
    Icon for Post Partisan rankPost Partisan
    3 years ago

    PaulDBrown, i didn't follow, where are you asking me to use sumx.Are they for these measures:-

    Late Return Temp =
    VAR _ReturnDate=
    IF(
        ISBLANK(MAX('Case Order Fact'[Act Return Date])),
        TODAY(),
        MAX('Case Order Fact'[Act Return Date])
    )
    VAR _Diff=
        DATEDIFF(MAX('Case Order Fact'[Expected Return Date]),_ReturnDate,DAY)
    RETURN
        SWITCH(
            TRUE(),
            ISBLANK(MAX('Case Order Fact'[Expected Return Date])),BLANK(),
            _Diff>0,1,
            0
        )
     
     
    Correct Return Temp =
    VAR _ReturnDate=
    IF(
        ISBLANK(MAX('Case Order Fact'[Act Return Date])),
        TODAY(),
        MAX('Case Order Fact'[Act Return Date])
    )
    VAR _Diff=
    DATEDIFF(MAX('Case Order Fact'[Expected Return Date]),_ReturnDate,DAY)

    RETURN
    SWITCH(
        TRUE(),
        ISBLANK(MAX('Case Order Fact'[Expected Return Date])),BLANK(),
        _Diff<0,1,
        0
    )
     
     
    Also all the columns that I am using are from the same table, earlier i had used sales rep alone from another sales hierarchy dim but now i brought in the same fact from where returnand returned dates are used.
    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      3 years ago

      The measures you have posted are to then calculate the correct totals using SUMX:

      Correct return = 
SUMX(fTable, [CORRECT Return Temp])
      Late return = 
SUMX(fTable, [LATE Return Temp])

      and finally

      % Late returns =
IF (
    ISINSCOPE ( fTable[Sales Rep] ),
    BLANK (),
    DIVIDE ( [Late return], [Correct return] + [Late return] )
)

      If you have a Sales Rep Dimension table, you should be using it in the visual (it's more efficient). If so, you need to reference this dimension table in the SUMX measures.

       

      • Avivek's avatar
        Avivek
        Icon for Post Partisan rankPost Partisan
        3 years ago

        PaulDBrown, this is exactly how we did, sales rep is the created name from the same table Case Order Fact, all the columns in measure are from same table. So am I missing something?

        Why is it then not giving the right value?

  • Avivek's avatar
    Avivek
    Icon for Post Partisan rankPost Partisan
    3 years ago

    All the measures will be as below:

    Correct Return Temp =
    VAR _ReturnDate=
    IF(
        ISBLANK(MAX('Case Order Fact'[Act Return Date])),
        TODAY(),
        MAX('Case Order Fact'[Act Return Date])
    )
    VAR _Diff=
    DATEDIFF(MAX('Case Order Fact'[Expected Return Date]),_ReturnDate,DAY)
    RETURN
    SWITCH(
        TRUE(),
        ISBLANK(MAX('Case Order Fact'[Expected Return Date])),BLANK(),
        _Diff<0,1,
        0)
     
    Correct return = SUMX('Case Order Fact',[Correct Return Temp])
     
    Late Return Temp =
    VAR _ReturnDate=
    IF(
        ISBLANK(MAX('Case Order Fact'[Act Return Date])),
        TODAY(),
        MAX('Case Order Fact'[Act Return Date])
    )
    VAR _Diff=
        DATEDIFF(MAX('Case Order Fact'[Expected Return Date]),_ReturnDate,DAY)
    RETURN
        SWITCH(
            TRUE(),
            ISBLANK(MAX('Case Order Fact'[Expected Return Date])),BLANK(),
            _Diff>0,1,
            0
        )
    Late return =
    SUMX('Case Detail Fact',[Late Return Temp])
     
    % Late Returns =
    IF(
        ISINSCOPE('Case Order Fact'[CREATED_NAME]),
        BLANK(),
        DIVIDE([Late return],[Correct return]+[Late return])
    )