Forum Discussion

Jay20024's avatar
Jay20024
Icon for Helper I rankHelper I
1 year ago
Solved

Cumulated value issue and bar chart

Hi folks,

Got a classic 2 months dataset with transaction dates (Jan & Feb), suppliers and amount, linked to a DimDate table.

Created a button to switch between Running cost & accumulated.

Add the Supplier field as legend

 

Works partialy in a pivot table, Total accumulated is correctly calculated in Feb, but not the details

Doesn't work in a barchart,  it accumulates only suppliers with amounts in Jan & Fev

Supplier 05 & 07 have no data in Fev, so they are not taken in account in the barchart in Feb.

How can I fix that ?

 

I thank you in advance folks

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below.

     

     

     

     

     

     

     

    Sales: = 
SUM( sales[sales] )

     

     

    Sales cumulate: = 
IF (
    [Sales:],
    CALCULATE (
        [Sales:],
        FILTER (
            ALL ( 'calendar'[Year-Month sort], 'calendar'[Year-Month] ),
            'calendar'[Year-Month sort] <= MAX ( 'calendar'[Year-Month sort] )
        )
    )
)

     

  • Jay20024's avatar
    Jay20024
    1 year ago

    Once again, Jihwan_Kim, you made my day. Seeing your cumulated measure, I realized that I got a condition in my measure, blacklisting blank values ! Just removed it and it works like a charm. Many thanks for your expertise !

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below.

     

     

     

     

     

     

     

    Sales: = 
SUM( sales[sales] )

     

     

    Sales cumulate: = 
IF (
    [Sales:],
    CALCULATE (
        [Sales:],
        FILTER (
            ALL ( 'calendar'[Year-Month sort], 'calendar'[Year-Month] ),
            'calendar'[Year-Month sort] <= MAX ( 'calendar'[Year-Month sort] )
        )
    )
)

     

    • Jay20024's avatar
      Jay20024
      Icon for Helper I rankHelper I
      1 year ago

      Once again, Jihwan_Kim, you made my day. Seeing your cumulated measure, I realized that I got a condition in my measure, blacklisting blank values ! Just removed it and it works like a charm. Many thanks for your expertise !

  • d_m_LNK's avatar
    d_m_LNK
    Icon for Super User rankSuper User
    1 year ago

    Is it as simple as adding the Month column from you date/calendar table onto the X Axis of your bar chart visual and the column portion of the matrix visual?  

    If it's something more complex can you share the measure you are using to evaluate in both visuals?