Forum Discussion
Help with showing counts without summing or using MAX?
Honestly, I have no idea if this is possible but would like to see if anyone has any solutions. I have a matrix and a bar graph showing counts of vehicles by month and year and I have them filtered by Branch using a slicer and a month slicer. For example, I have three branches: Portland, Nashville, Seattle. When I select all three branches, the Max calculation just shows the highest branch total which is Portland instead of adding all three branches to give me the total number of vehicles. Another issue with using MAX is it only works if the number of vehicles increases by every month but sometimes a vehicle is out of commission but it'll still just take the max number. So I tried to remedy this using SUM but when collapsing the Matrix, it just adds all the vehicles together making it seem like we have multiple times the vehicles we actually do. And I'm not able to use SUM for my bar graph. Does anyone have any solutions that could solve these issues?
My data source
Figured it out!
Vehicle Count Branch =LASTNONBLANKVALUE('AF Miles Vehicles'[Month], SUM('AF Miles Vehicles'[Count of Vehicle]))I used this calculation using the month number to sort by and then the sum as my second parameter. I used this page for help : https://exceleratorbi.com.au/lastnonblank-explained/
2 Replies
Figured it out!
Vehicle Count Branch =LASTNONBLANKVALUE('AF Miles Vehicles'[Month], SUM('AF Miles Vehicles'[Count of Vehicle]))I used this calculation using the month number to sort by and then the sum as my second parameter. I used this page for help : https://exceleratorbi.com.au/lastnonblank-explained/Just wanted to give some more information
April had less vehicles than March but when collapsing the matrix, it used March's value since it was the max value. But after using my formula above:
It is now taking April's vehicle count as the last non blank value.
And when I select all my branches, it adds up each branch's last value calculating the total!