Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
omustu
Frequent Visitor

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('SharepointTable'[Status Code Rejected]),
    FILTER('SharepointTable', 'SharepointTable'[Date created] = min_date && 'SharepointTable'[UID] = SELECTEDVALUE('SharepointTable'[UID]))
)
VAR max_status = CALCULATE(
    MIN('SharepointTable'[Status Code Rejected]),
    FILTER('SharepointTable', 'SharepointTable'[Date created] = max_date && 'SharepointTable'[UID] = SELECTEDVALUE('SharepointTable'[UID]))
)
VAR idea_number = SELECTEDVALUE('SharepointTable'[Ide Number])
VAR total_savings = CALCULATE(
    SUM('SharepointTable'[12 Month Savings from Incorp Date at 100% USD]),
    FILTER(
        ALL('SharepointTable'),
        'SharepointTable'[Date created] = max_date && 'SharepointTable'[Ide Number] = idea_number
    )
)
RETURN
IF(min_status <> BLANK() && max_status <> BLANK() && min_status <> max_status, total_savings)

I put this measure in the Values field in my matrix. however, when i try sort with respect to this calculated measure, it doesn't work properly. 
omustu_0-1734950276357.png

As you can see from this screenshot, the number 1675,660 should have been at the top of the list, however 511,915 is. It is most likely related with regional settings and number formattings however i have tried everything, like using Enligsh(US) as region and also my own country as the region but it always results the same. Both my DAX measure and 12 Month Savings from Incorp Date at 100% USD column is in decimal format.
Any idea how i can fix this issue?

Thank you for reading.

1 ACCEPTED SOLUTION

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)
)

vxianjtanmsft_0-1735114447879.png

 

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.

 

View solution in original post

5 REPLIES 5
omustu
Frequent Visitor

Hi, @v-xianjtan-msft @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:

omustu_0-1735029183394.png

The fields of my pbix file:

omustu_1-1735029237097.png

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. 








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)
)

vxianjtanmsft_0-1735114447879.png

 

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.

 

Hi, @v-xianjtan-msft 
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.

suparnababu8
Super User
Super User

Hi @omustu 

 

Can you pls provide a sample data with out any sensitive information to solve your problem. Bcz your Image also not clearly visible what filed you are used as categoricals.

 

Thanks

v-xianjtan-msft
Community Support
Community Support

Hi @omustu 

 

In order for us to get a clearer understanding of your problem, please provide us with sample data that can fully cover your problem, a pbix file would be great. (Remember to remove private information)

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

 

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.