Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 :
Could I please request you all to help me debug this issue?
Solved! Go to Solution.
@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!
@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!
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.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |