Forum Discussion

omustu's avatar
omustu
Frequent Visitor
1 year ago
Solved

Number sorting not working properly in calculated measure

Hello everyone, I have a calculated measure with the following DAX Formula: Measure1 = VAR max_date = [LatestDate] VAR min_date = [SecondLatestDate] VAR min_status = CALCULATE(     MIN('...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi omustu 

     

    According to your pbix screenshots, I think it may be caused by the Cost_Saving measure having blank subtotals in the matrix. Power BI may fail to sort if the measure is blank in certain rows or columns when sorting.
    When I added the subtotal calculations to your measure, the sorting started working correctly.

    Cost_Saving = 
VAR max_date = [LatestDate]
VAR min_date = [SecondLatestDate]
VAR min_status = CALCULATE(
    MIN(Sheet1[Status]),
    FILTER(Sheet1, Sheet1[Date] = min_date && Sheet1[UID] = SELECTEDVALUE(Sheet1[UID]))
)
VAR max_status = CALCULATE(
    MIN(Sheet1[Status]),
    FILTER(Sheet1, Sheet1[Date] = max_date && Sheet1[UID] = SELECTEDVALUE(Sheet1[UID]))
)
VAR idea_number = SELECTEDVALUE(Sheet1[Idea Number])
VAR total_savings = CALCULATE(
    SUM(Sheet1[Saving Cost]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Date] = max_date && Sheet1[Idea Number] = idea_number
    )
)
VAR is_total = HASONEVALUE(Sheet1[UID]) = FALSE() || HASONEVALUE(Sheet1[Idea Number]) = FALSE()
RETURN
IF(
    is_total,
    total_savings,
    IF(min_status <> BLANK() && max_status <> BLANK() && min_status <> max_status, total_savings)
)

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

omustu's avatar
omustu
Frequent Visitor
1 year ago

Hi, Anonymous suparnababu8 

I am sharing a sample table as you requested, for some reason i couldn't upload the sample pbix file i prepared but i will include its screenshot also.
The sample excel file is like below:

UIDIdea NumberDescriptionStatusSaving CostDate
10910Description310910Description31333505,025.12.2024
10910Description110910Description11317323,9095.12.2024
10910Description210910Description23286385,4855.12.2024
10910Description510910Description52126889,1815.12.2024
10915Description310915Description33164080,5265.12.2024
10560Description310560Description34232164,4215.12.2024
10430Description110430Description14328325,4175.12.2024
10430Description510430Description52179782,7475.12.2024
10910Description310910Description33262746,25412.12.2024
10910Description110910Description15312785,63212.12.2024
10910Description210910Description2Rejected316485,1612.12.2024
10910Description510910Description5Rejected19780,367112.12.2024
10430Description110430Description16234716,72712.12.2024
10430Description510430Description5Rejected340590,03912.12.2024
10340Description310340Description3123709,346212.12.2024
10280Description410280Description45190067,13112.12.2024


The Pbix screenshot is below:

The fields of my pbix file:

The formula for Cost_Saving measure:

Cost_Saving =
VAR max_date = [LatestDate]
VAR min_date = [SecondLatestDate]

VAR min_status = CALCULATE(
    MIN(Sheet1[Status]),
    FILTER(Sheet1, Sheet1[Date] = min_date && Sheet1[UID] = SELECTEDVALUE(Sheet1[UID]))
)
VAR max_status = CALCULATE(
    MIN(Sheet1[Status]),
    FILTER(Sheet1, Sheet1[Date] = max_date && Sheet1[UID] = SELECTEDVALUE(Sheet1[UID]))
)
VAR idea_number = SELECTEDVALUE(Sheet1[Idea Number])
VAR total_savings = CALCULATE(
    SUM(Sheet1[Saving Cost]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Date] = max_date && Sheet1[Idea Number] = idea_number
    )
)
RETURN
IF(min_status <> BLANK() && max_status <> BLANK() && min_status <> max_status, total_savings)


The formula for Status_Change_Daily measure:
Status_Change_Daily =

var max_date = [LatestDate]
var min_date = [SecondLatestDate]

var min_status = CALCULATE(min(Sheet1[Status]) , FILTER(Sheet1 , Sheet1[Date]=min_date && Sheet1[UID]=SELECTEDVALUE(Sheet1[UID])))
var max_status = CALCULATE(min(Sheet1[Status]) , FILTER(Sheet1 , Sheet1[Date]=max_date && Sheet1[UID]=SELECTEDVALUE(Sheet1[UID])))
return
if(min_status <> blank() && max_status <> blank() && min_status <> max_status, min_status &  "-->" & max_status)

The description of what im trying to achieve:
If for the same UID's for different date's, there has been a change in Status value, i want to sum up their Saving Cost value (only for the UID's with the same Idea Number) and display it. I am able to do these but the sorting with respect to Cost_Saving measure doesn't work properly. 








  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi omustu 

     

    According to your pbix screenshots, I think it may be caused by the Cost_Saving measure having blank subtotals in the matrix. Power BI may fail to sort if the measure is blank in certain rows or columns when sorting.
    When I added the subtotal calculations to your measure, the sorting started working correctly.

    Cost_Saving = 
VAR max_date = [LatestDate]
VAR min_date = [SecondLatestDate]
VAR min_status = CALCULATE(
    MIN(Sheet1[Status]),
    FILTER(Sheet1, Sheet1[Date] = min_date && Sheet1[UID] = SELECTEDVALUE(Sheet1[UID]))
)
VAR max_status = CALCULATE(
    MIN(Sheet1[Status]),
    FILTER(Sheet1, Sheet1[Date] = max_date && Sheet1[UID] = SELECTEDVALUE(Sheet1[UID]))
)
VAR idea_number = SELECTEDVALUE(Sheet1[Idea Number])
VAR total_savings = CALCULATE(
    SUM(Sheet1[Saving Cost]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Date] = max_date && Sheet1[Idea Number] = idea_number
    )
)
VAR is_total = HASONEVALUE(Sheet1[UID]) = FALSE() || HASONEVALUE(Sheet1[Idea Number]) = FALSE()
RETURN
IF(
    is_total,
    total_savings,
    IF(min_status <> BLANK() && max_status <> BLANK() && min_status <> max_status, total_savings)
)

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • omustu's avatar
      omustu
      Frequent Visitor
      1 year ago

      Hi, Anonymous 
      This one actually worked. Thank you very much 😊, i thought it was all about number formatting but turns out it has nothing to do with it.