Running total for a row avoiding NULL values

[juliausha]

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!

[v-lili6-msft]

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

[juliausha]

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-msft · Accepted solution]

hi, @juliausha 

Please drag "VisitedShops" field from VisitedShops table into Columns Value.

Best Regards,

Lin

[juliausha]

Ah, great, it works now! Thanks so much @v-lili6-msft !

[juliausha]

Ah, great, it works now! Thanks so much @v-lili6-msft !