cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webportal Member
Member

Display empty values in area chart

I've given up trying to define a measure that returns the bank account balance and cash flow, even for days when there are no movements. After lots of different combinations, I realized it's not worth the effort.

Therefore, I'm living with BLANK for those days, which in a matrix is OK. 

However, in a Area Chart, I'd like to show the balance for all the days within the dates range. But this is showing empty values for thoses days with no movements:

Capturar.PNG

This doesn't look good with empty spots...

 

Also, why are the three series not accumulating? That's the whole idea of this chart, right?

 

Thanks for helping!

1 ACCEPTED SOLUTION

Accepted Solutions
webportal Member
Member

Re: Display empty values in area chart

Hi,

 

I've figured out I must have a value for each day in my dates range for the ending balance.

Otherwise, charts and visuals in general will look terrible.

 

This is the expression I've managed to find:

Saldo em EUR = 
IF (
    AND (
        'Consolidado'[DataCorrente] <= 'Consolidado'[DataMáxima];
        'Consolidado'[DataCorrente] >= 'Consolidado'[DataMínima]
    );
    CALCULATE (
        0 + SUM ( 'Consolidado'[Fluxo em EUR (intermédio)] );
        FILTER (
            ALL ( 'Calendário'[Data] );
            'Calendário'[Data] <= MAX ( 'Calendário'[Data] )
        )
    )
)

Initially, a simple measure but required to define intermediate calculations.

 

Now, the charts are looking better:

Capturar2.PNGCapturar3.PNG

6 REPLIES 6
dedelman_clng New Contributor
New Contributor

Re: Display empty values in area chart

2nd question first - use a stacked area chart (you just have a standard area chart)

 

As to the 1st question, can you replace BLANK with 0 in the data?  There have been other dicussions that the area/stacked are chart leaves whitespots when there is missing data - it's a "bug" that seems to be low priority to be addressed.

 

You could also use a stacked column chart, which will show basically the same thing as the stacked area chart, but with "gaps" in between each X value.

 

area.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps

David

webportal Member
Member

Re: Display empty values in area chart

Thanks a lot for your support.


The thing is, if you check your bank account on a day without transactions, you should still be able to see the balance (not a blank).

Super User
Super User

Re: Display empty values in area chart

Is your X-Axis Continuous?

dedelman_clng New Contributor
New Contributor

Re: Display empty values in area chart

So it sounds like your value needs to be a running total?  Here is a DAX pattern for running total (assume Table1 and DateTable have a Many-to-1 relationship:

 

RunningMeasure = CALCULATE (
    SUM ( 'Table1'[Value Column]),
    FILTER (
        ALL ( 'DateTable'[Date] ),
        'Table1'[Date] <= MAX ( 'DateTable'[Date] )
    )
)

 

Hope this helps

David

webportal Member
Member

Re: Display empty values in area chart

Hi,

 

I've figured out I must have a value for each day in my dates range for the ending balance.

Otherwise, charts and visuals in general will look terrible.

 

This is the expression I've managed to find:

Saldo em EUR = 
IF (
    AND (
        'Consolidado'[DataCorrente] <= 'Consolidado'[DataMáxima];
        'Consolidado'[DataCorrente] >= 'Consolidado'[DataMínima]
    );
    CALCULATE (
        0 + SUM ( 'Consolidado'[Fluxo em EUR (intermédio)] );
        FILTER (
            ALL ( 'Calendário'[Data] );
            'Calendário'[Data] <= MAX ( 'Calendário'[Data] )
        )
    )
)

Initially, a simple measure but required to define intermediate calculations.

 

Now, the charts are looking better:

Capturar2.PNGCapturar3.PNG

Highlighted
capta12 Visitor
Visitor

Re: Display empty values in area chart

Thank you for a good solution! However I can't get it to work on my dataset. Would you mind to share the .pbix? It would help to get an overview of the measure. 

 

Kind regards,

Anders