Forum Discussion

juliausha's avatar
juliausha
Helper I
7 years ago
Solved

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-msft's avatar
    v-lili6-msft
    Community 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

     

    • juliausha's avatar
      juliausha
      Helper 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-msft's avatar
        v-lili6-msft
        Community Support

        hi, juliausha 

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

         

        Best Regards,

        Lin