cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Dax Min Calculation for Card returning incorrect value

Hi Team,

I am trying to calculate the minimum value to setup the y axis scale. However, it is returning the incorrect value.

I have a measure called delta which returns current year minus last year sales. I am preparing a stacked column chart.

In the below card visual, I am calculating the lowest negative(only considering values less than 0) value amonst all years and by looking at the chart. it should return -120 as it is for 2023. but my calculation is returning -80.

Calculation is :

Current Year = SUM(FctTable[Amount])
Last Year = CALCULATE([Current Year],SAMEPERIODLASTYEAR(DimDate[Date]))
Delta = [Current Year]-[Last Year]
MINIMUMCALC =
VAR _Test =
FILTER (
SUMMARIZE ( ALLSELECTED( FctTable ), DimDate[year], DimProd[product] ),
"@TestAmount", [Delta] )
,
[@TestAmount] < 0
)
VAR _result =
GROUPBY ( _Test, DimDate[Year], "@NegativeCalc", SUMX ( CURRENTGROUP (), [@TestAmount] ) )
RETURN
MINX ( _result, [@NegativeCalc] )

Could I please request you all to help me debug this issue?

1 ACCEPTED SOLUTION
Community Support

Hi @KKVerma ,

I used the example data you gave and got the expected result. Please check if there is anything that can be improved.

The modified measure is as follows:

``````MINIMUMCALC =
VAR _Test =
//FILTER (
//SUMMARIZE ( ALLSELECTED( FactTable ),DimDate[year], DimProd[Product] ),
//"@TestAmount", [Measure])
//,
//[@TestAmount] < 0
//)
VAR _year =
SUMMARIZE ( 'DimDate', 'DimDate'[Year] )
VAR _product =
SUMMARIZE ( 'DimProd', 'DimProd'[Product] )
RETURN
FILTER (
FILTER (
CROSSJOIN ( _year, _product ),
'DimProd'[Product] IN SELECTCOLUMNS ( 'FactTable', 'FactTable'[Product] )
),
"@TestAmount", 'DimDate'[Delta]
),
[@TestAmount] < 0
)
VAR _result =
GROUPBY (
_Test,
DimDate[Year],
"@NegativeCalc", SUMX ( CURRENTGROUP (), [@TestAmount] )
)
RETURN
MINX ( _result, [@NegativeCalc] )``````

Since the example is missing 2023 for product A in the data, the commented out code yields the following table which don’t have amount of -120:

So we choose to use the CROSSJOIN function to get the combination of product and year. The modified MINIMUMCALC then returns a minimum value of -120:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

4 REPLIES 4
Community Support

Hi @KKVerma ,

I used the example data you gave and got the expected result. Please check if there is anything that can be improved.

The modified measure is as follows:

``````MINIMUMCALC =
VAR _Test =
//FILTER (
//SUMMARIZE ( ALLSELECTED( FactTable ),DimDate[year], DimProd[Product] ),
//"@TestAmount", [Measure])
//,
//[@TestAmount] < 0
//)
VAR _year =
SUMMARIZE ( 'DimDate', 'DimDate'[Year] )
VAR _product =
SUMMARIZE ( 'DimProd', 'DimProd'[Product] )
RETURN
FILTER (
FILTER (
CROSSJOIN ( _year, _product ),
'DimProd'[Product] IN SELECTCOLUMNS ( 'FactTable', 'FactTable'[Product] )
),
"@TestAmount", 'DimDate'[Delta]
),
[@TestAmount] < 0
)
VAR _result =
GROUPBY (
_Test,
DimDate[Year],
"@NegativeCalc", SUMX ( CURRENTGROUP (), [@TestAmount] )
)
RETURN
MINX ( _result, [@NegativeCalc] )``````

Since the example is missing 2023 for product A in the data, the commented out code yields the following table which don’t have amount of -120:

So we choose to use the CROSSJOIN function to get the combination of product and year. The modified MINIMUMCALC then returns a minimum value of -120:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Regular Visitor

Thank you so much. I understand that there is no data for 2023 in the dataset. But the measure is returning data for 2023. And my calculations should have returned the correct results but it is not which is strange. Thank you so much.

Super User
``I am trying to calculate the minimum value to setup the y axis scale.``

Can you please explain this a bit more?  General guidance is to start the y axis at zero, to avoid misrepresenting the data.

Regular Visitor

Basically from the visual, I want to calculate for which year the lowest negative value is there. In my case in the visual I want to consider only values which are negative in visual representation of stacked bar chart. Therefore, the measure should return the total value for 2023 but it's returning incorrect value.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors