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, 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.