Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
KKVerma
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.

KKVerma_0-1722801276551.png

 

KKVerma_0-1722800665796.png

 

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] )

 

KKVerma_1-1722800715582.png

 

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

@dax 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

vlinhuizhmsft_0-1723014872424.png

 

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:

vlinhuizhmsft_1-1723014897661.png

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!

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

 

vlinhuizhmsft_0-1723014872424.png

 

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:

vlinhuizhmsft_1-1723014897661.png

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!

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.

lbendlin
Super User
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.

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.