Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
Hello everyone,
I have a calculated measure with the following DAX Formula:
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.
Solved! Go to 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)
)
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 @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:
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.
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.
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
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.
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |