Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I am new to power BI and are trying to create a graph that shows the sum of total customers per year.
Now I have created a measure that counts all customers and have a chart that display the increase per year:
I want it to be the sum. so line 1, line 2(1+2), line 3(1+2+3), line 4(1+2+3+4) and so on.
How can I create this
Thanks for the help
Solved! Go to Solution.
Hi @Anonymous ,
You can calculate cumulative count of customer using Measure as well:
Running Total MEASURE = CALCULATE ( Count ( 'Table'[CustomerColumn] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) )
Please give Kudos to this effort and also accept this as a solution so other can take help from it!
HI @Anonymous,
As per my understanding you want Cumulative count of the customer.
Running Total COLUMN = CALCULATE ( Count ( 'Table'[CustomerColumn] ), ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
Please give Kudos to this effort and also accept this as a solution so other can take help from it!
Hi @Anonymous ,
You can calculate cumulative count of customer using Measure as well:
Running Total MEASURE = CALCULATE ( Count ( 'Table'[CustomerColumn] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) )
Please give Kudos to this effort and also accept this as a solution so other can take help from it!
Hi @Tahreem24
Thanks for the help!
Is there a way to add more filters to the query? I need it to be onlye active cusomers (status = 1)
I cannot get it to work
Thanks
Hi @Anonymous ,
Yes you can use AND function in filter like below:
Running Total MEASURE = CALCULATE ( Count ( 'Table'[CustomerColumn] ), FILTER ( (ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] )) && Status=1) ) )
OR Use the 2 different Filter function like below:
CALCULATE ( SUM ( Table[amount] ), FILTER ( Table, Table[Column1] = ??? ), FILTER ( Table, Table[Column2] = ??? ) )
Please give Kudos to this effort!
Hi thanks for your reply
The last one wont work as a count guids that are strings.
In the first example I have this query:
Running Total Coumn = CALCULATE(COUNT(Contact[contactid]);FILTER(ALL(Contact);Contact[mg_customerstartdate] <= MAX (Contact[mg_customerstartdate])) && Contact[statuscode_display]="Aktiv")
I get this error message:
A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Do you know what is wrong?
Thanks
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |