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 ))RETURNIF(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. 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.

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 TangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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('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.

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.

Need Help

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.

Number sorting not working properly in calculated measure.pbix51 KB

5 Replies

Anonymous
Not applicable
1 year ago
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.
suparnababu8
Super User
1 year ago
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

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:

UID	Idea Number	Description	Status	Saving Cost	Date
10910Description3	10910	Description3	1	333505,02	5.12.2024
10910Description1	10910	Description1	1	317323,909	5.12.2024
10910Description2	10910	Description2	3	286385,485	5.12.2024
10910Description5	10910	Description5	2	126889,181	5.12.2024
10915Description3	10915	Description3	3	164080,526	5.12.2024
10560Description3	10560	Description3	4	232164,421	5.12.2024
10430Description1	10430	Description1	4	328325,417	5.12.2024
10430Description5	10430	Description5	2	179782,747	5.12.2024
10910Description3	10910	Description3	3	262746,254	12.12.2024
10910Description1	10910	Description1	5	312785,632	12.12.2024
10910Description2	10910	Description2	Rejected	316485,16	12.12.2024
10910Description5	10910	Description5	Rejected	19780,3671	12.12.2024
10430Description1	10430	Description1	6	234716,727	12.12.2024
10430Description5	10430	Description5	Rejected	340590,039	12.12.2024
10340Description3	10340	Description3	1	23709,3462	12.12.2024
10280Description4	10280	Description4	5	190067,131	12.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

Not applicable

1 year ago

Hi omustu

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.

Number sorting not working properly in calculated measure.pbix51 KB

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.

Forum Discussion

Number sorting not working properly in calculated measure

5 Replies

Related content

Sorting

sort a calculated table

sorting matrix

Table Sort , sort order , comparer

YTD calculation not working properly