Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
hi, @juliausha
Please drag "VisitedShops" field from VisitedShops table into Columns Value.
Best Regards,
Lin
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
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!
hi, @juliausha
Please drag "VisitedShops" field from VisitedShops table into Columns Value.
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |