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
DaveNic
Frequent Visitor

2 Cumulative Totals in Line Chart

Hi everyone,

Im new to Power BI and first time posting here.

I am looking to try and create a line chart that has cumulative (running) totals on both X and Y axis. Essentially what I want to plot is the number of customer surveys that a company has had completed (X-axis) against the number of those surveys that answered a question ('Would you recommend the company?)' positively (Y-axis). The steeper the curve, the higher the percentage of positive responses. Surveys are run in yearly cycles, so ideally I would then like to plot previous years performance on the same chart for comparison.

 

I have been able to produce cumulative measures for both the number of surveys and 'Yes Count', using something like the below, but these track against dates, which is not really what I'm interested in:

 

Cumulative Surveys 21/22 = 
CALCULATE (
    [Survey Count],
    FILTER (
        ALL ('Date'),
        'Date'[Date] <= MAX ( 'Customer Satisfaction Master Table'[Response Date] )
    )
)

 

 

Can anyone give me a steer on this please, if it is possible, or even if its a bad idea?!

 

Thanks

Dave

5 REPLIES 5
DaveNic
Frequent Visitor

Does anyone have any further thoughts on how to go about this please?

Hi @DaveNic ,

 

According to your statement, you want to create a line chart that has cumulative (running) totals on both X and Y axis, the number of customer surveys that a company has had completed in X-axis against the number of those surveys that answered a question ('Would you recommend the company?)' positively in Y-axis.

Firstly, two cumulative (running) totals are calcualted by measures, however Power BI doesn't support us to add measures in Axis. Secondly, I think both measures are based on [Date]column, so at least there should be a [Year] or [Date] column in your visual. So I think this is not a good idea. I suggest you to compare two cumulative (running) totals individually by two linds or columns.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-rzhou-msft 

Thanks for your input. You are correct in your assumptions. I have both the running total of responses and of positive responses in measures at the moment and as such I'm unable to add them to both the x and y axis of the chart. 

Plotting two lines against the date is the only option that I've seen that works in Power Bi so far, but in terms of being able to read and compare the overall performance on previous years, I dont think this is intutive. e.g as below (just one year plotted):

DaveNic_0-1646988325169.png

If the same data for previous years was also plotted it would get cluttered very quickly, and not really present the data in a way that could be readily understood.

 

What I'm trying to acheive is roughly as the below from Excel:

DaveNic_1-1646989807221.png

In other words, to be able to look at the charts and see at a glance whether performance is better or worse than it was at this point last year in terms of the numbers of surveys received.

 

Thanks,

Dave

littlemojopuppy
Community Champion
Community Champion

@DaveNic  welcome to the Power BI Community Forum!

You asked if this is a bad idea.  No it's not.  Seems a perfectly reasonable way to evaluate survey responses.  But a question: would you want the most replied to question shown first (which would produce a Pareto-like effect on the cumulative total)?  Or generally speaking...how would you order the questions?

 

Can you provide some sample data to work with?

Thanks @littlemojopuppy 

There is only one question that would be evaluated, with the simple yes/no answer, so it would just be a cumulative count of the 'Yes' responses to this question vs the total number of valid responses that I would like to plot.

 

I'm not exactly sure how I would post sample data here, but if you imagine a table with just 3 columns, (Cust ID, Response Date, Recommend (Y/N) ) I'd imagine that's all that would be needed.

 

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.