cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sulej Frequent Visitor
Frequent Visitor

Cumulative chart

Hi all,

 

I am new to PowerBI tool and I just wanted to test it for automation of the standard test that I do normally in Excel. 

 

 

CURRENT EXCEL SOLUTION

 

It's quite simple - it's a chart as follows: 

 

Y axis - monetary value

X axis - month.year  (e.g. 12.2018)

values - number of lines on the chart depends on the number of salesmen. For each month.year X, Y equals to this month sales + cumulative values from previous months

 

So in result I obtain few cumulative lines on one chart and those lines show me which salesman sales dynamic is the "strongest".

 

For now, in Excel, I simply make a pivot and then I copy this table to another sheet, then I apply appropriate sum formula and then I just make a line chart of the outcome.

 

POWERBI MODEL?

 

The idea behind is to build one file that will draw for me this cumulative chart based on input files (.xls) which I will replace with another set of data for another scope.

 

Entry data are SAP tables, therefore HEADER table and LINE_ITEM table.

 

In HEADER I have: 

  1. Salesman name
  2. Sales date
  3. Sales order number

 

In LINE_ITEM table I have:

  1. Sales order number
  2. Line items sold (many lines with the same sales order no) 
  3. Value of each line

 

In excel I just vlookup those three HEADER columns into LINE_ITEM table and the proceed to pivot. 

 

In PowerBI I have 4 tables loaded:

  1. HEADER
  2. LINE_ITEM
  3. Salesman code = name reference
  4. Customer code = name reference

 

And here's the point: I arrive to proper PowerBI matrix having salesman, date of sales and value of sales order. But this is not cumulated data. My questions is how to achieve the same outcome in PowerBI as in excel? If can cumualte the data in matrix then, I would just apply line chart as in Excel. Do you have any ideas how to do that or maybe, can you propose me other approach? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Cumulative chart

Hi @sulej,

 

Change the relationshiop between date table and the 2LINE ITEMS + data_from_HEADER table to single. And create the measure as below.

 

Measure 3 = 
var m =CALCULATE(MAX('2LINE ITEMS + data_from_HEADER'[Yearmonth]),ALL('2LINE ITEMS + data_from_HEADER'))
VAR re =
    CALCULATE(SUM('2LINE ITEMS + data_from_HEADER'[VALUE]),FILTER(ALLEXCEPT('2LINE ITEMS + data_from_HEADER','2LINE ITEMS + data_from_HEADER'[SALESMAN]),'2LINE ITEMS + data_from_HEADER'[Yearmonth]
            <= MAX('CALENDAR'[YearMonth])))
RETURN
   IF (
        MAX ( 'CALENDAR'[YearMonth] ) > m,
        BLANK (),
        IF ( ISBLANK ( re ), 0, re )
    )

Then we can get the result same as yours.

 

22222.PNG

 

 

Also please find the pbix as attached.

 

Regards,

Frank

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

View solution in original post

7 REPLIES 7
Community Support Team
Community Support Team

Re: Cumulative chart

Hi @sulej,

 

Could you please share your sample data and excepted result to me. You can upload your files to onedrive and share the link here.

 

Reagrads,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sulej Frequent Visitor
Frequent Visitor

Re: Cumulative chart

Hi @v-frfei-msft,

 

Please find below the link to sample excel file, with the steps that I normally perform and the output result. In this case there are only few months. 

 

https://1drv.ms/x/s!Al4POElXYEpThOE7uGCVRFvf-dHUfw

 

 

 

 

Community Support Team
Community Support Team

Re: Cumulative chart

Hi @sulej,

 

Please check the following steps as below.

 

1. Create a calculated column.

 

Yearmonth = YEAR('2LINE ITEMS + data_from_HEADER'[SALESDATE])*100+MONTH('2LINE ITEMS + data_from_HEADER'[SALESDATE])

2. To create a measure as below.

 

Measure = 
CALCULATE (
    SUM ( '2LINE ITEMS + data_from_HEADER'[VALUE] ),
    FILTER (
        ALLEXCEPT (
            '2LINE ITEMS + data_from_HEADER',
            '2LINE ITEMS + data_from_HEADER'[SALESMAN]
        ),
        '2LINE ITEMS + data_from_HEADER'[Yearmonth]
            <= MAX ( '2LINE ITEMS + data_from_HEADER'[Yearmonth] )
    )
)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Support Team
Community Support Team

Re: Cumulative chart

Hi @sulej,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sulej Frequent Visitor
Frequent Visitor

Re: Cumulative chart

Hi @v-frfei-msft,

 

I have a problem - my outcome chart doesn not keep consistency of lines - there are signle dots for some series of data. I thoroughly, applied you solution to my real data. I marked in yellow the "missing line"

 

CHART.JPG

Community Support Team
Community Support Team

Re: Cumulative chart

Hi @sulej,

 

Change the relationshiop between date table and the 2LINE ITEMS + data_from_HEADER table to single. And create the measure as below.

 

Measure 3 = 
var m =CALCULATE(MAX('2LINE ITEMS + data_from_HEADER'[Yearmonth]),ALL('2LINE ITEMS + data_from_HEADER'))
VAR re =
    CALCULATE(SUM('2LINE ITEMS + data_from_HEADER'[VALUE]),FILTER(ALLEXCEPT('2LINE ITEMS + data_from_HEADER','2LINE ITEMS + data_from_HEADER'[SALESMAN]),'2LINE ITEMS + data_from_HEADER'[Yearmonth]
            <= MAX('CALENDAR'[YearMonth])))
RETURN
   IF (
        MAX ( 'CALENDAR'[YearMonth] ) > m,
        BLANK (),
        IF ( ISBLANK ( re ), 0, re )
    )

Then we can get the result same as yours.

 

22222.PNG

 

 

Also please find the pbix as attached.

 

Regards,

Frank

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

View solution in original post

sulej Frequent Visitor
Frequent Visitor

Re: Cumulative chart

This is excellent!

 

Thank you 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 56 members 1,185 guests
Please welcome our newest community members: