topic Re: Dax Min Calculation for Card returning incorrect value in DAX Commands and Tips

Dax Min Calculation for Card returning incorrect value

KKVerma — Sun, 04 Aug 2024 19:55:49 GMT

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 (

ADDCOLUMNS (

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?

dax

Re: Dax Min Calculation for Card returning incorrect value

lbendlin — Sun, 04 Aug 2024 20:12:43 GMT

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.

Re: Dax Min Calculation for Card returning incorrect value

KKVerma — Mon, 05 Aug 2024 21:34:15 GMT

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.

Re: Dax Min Calculation for Card returning incorrect value

Anonymous — Wed, 07 Aug 2024 07:16:42 GMT

@lbendlin , thanks for your concern about this case.

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 ( //ADDCOLUMNS( //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 ( ADDCOLUMNS ( 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!

Re: Dax Min Calculation for Card returning incorrect value

KKVerma — Sat, 10 Aug 2024 18:42:44 GMT

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.