Forum Discussion
Running total for a row avoiding NULL values
Does anyone know how to calculate running total for a row making sure null values are avoided?
My example:
Each row represents amount of shops someone has in the neighbourhood, and columns represent amount of shops visited.
E.g. 18 people have 7 shops in their neighbourhood. From which 11 people visited 0 shops and 2 people visited 1 shop, etc.
I would like to get the running total of the row with fillowing result:
In order to calculate running total for each visited shop, I used the following measure:
Count of PersonId running total in VisitedShops =
CALCULATE(
DISTINCTCOUNT('MyData'[PersonId]),
FILTER(
ALLSELECTED('MyData'[VisitedShops]),
ISONORAFTER('MyData'[VisitedShops], MIN('MyData'[VisistedShops]), ASC)
)
)This measure gives me the wrong result because it skips null values and starts calculating running totals all over again:
Hope you are able to help! Thanks in advance!
hi, juliausha
Please drag "VisitedShops" field from VisitedShops table into Columns Value.
Best Regards,
Lin
5 Replies
- v-lili6-msftCommunity Support
hi, juliausha
This is same case I had solved yesterday, you could refer to it:
https://community.powerbi.com/t5/Desktop/cumulative-bar-graph/m-p/644428#M308814
For you case, just add a fact VisitedShops table, then create the relationship with "Mydata" table, then use [VisitedShops] from
VisitedShops table in Matrix and formula
Count of PersonId running total in VisitedShops = CALCULATE ( DISTINCTCOUNT ( 'MyData'[PersonId] ), FILTER ( ALLSELECTED ( VisitedShops[VisitedShops] ), ISONORAFTER ( 'VisitedShops'[VisitedShops], MIN ( 'VisitedShops'[VisitedShops] ), ASC ) ) )Result:
and here is pbix file, please try it.
Best Regards,
Lin
- juliaushaHelper I
Thank you for you reply v-lili6-msft !
I was tryng to follow your suggestions. I used your formula and created a column called 'Value' but for some reason it only gives '1' as a value. So at the end my result in 'Measure' is not matching yours... Could you please take a look at my pbix file?
Here's the link to pbix file in Dropbox: Mydata.pbix
Or WeTransfer link: Mydata.pbix
Thanks a lot!
- v-lili6-msftCommunity Support
hi, juliausha
Please drag "VisitedShops" field from VisitedShops table into Columns Value.
Best Regards,
Lin