Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create graph of sum of customers per year

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:

chart.png

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

1 ACCEPTED 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! 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

View solution in original post

5 REPLIES 5
Tahreem24
Super User
Super User

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! 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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! 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

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:

Spoiler
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!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.