Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    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.

     

     

     

    Also please find the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous,

 

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] )
    )
)

 

For more details, please check the pbix as attached.

 

Regards,

Frank

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous,

     

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


    Regards,
    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Anonymous,

       

      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.

       

       

       

      Also please find the pbix as attached.

       

      Regards,

      Frank

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        This is excellent!

         

        Thank you 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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"