Forum Discussion

KKVerma's avatar
KKVerma
Regular Visitor
2 years ago
Solved

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

"DAX "
CALCULATE
  • Anonymous's avatar
    Anonymous
    2 years ago

    @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!

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    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.

    • KKVerma's avatar
      KKVerma
      Regular Visitor
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    @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!

    • KKVerma's avatar
      KKVerma
      Regular Visitor
      2 years ago

      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.