CODEWITHSUNDEEP
powerbidax
CatherineL
CatherineL

Reputation: 23

Is there a way to create and display a running total by category in DAX?

I am trying to display a running total by category as a stacked bar chart. I have several pieces which are needed for a certain date. My Y-axis represents the number of pieces needed and the X-axis represents the deadlines. As a legend I would like to add the current state of each reference like this:

Categorized running total by date.

I achieved this by creating a column by status and by filling them with 0 and 1 but I would like to find faster way to do so, which would also work if someone added a category.

My need is to see at one glance at which point of the production process are my pieces and at which date they will be needed.

Using a measure I managed to get the running total, which looks like this :

Running total by date

The current measure is : 

CALCULATE(
    COUNTA('Historique LOB'[P/N14.Digits]);
    FILTER(
        ALLSELECTED('Historique LOB'[Besoin concaténé format date]);
        ISONORAFTER('Historique LOB'[Besoin concaténé format date]; MAX('Historique LOB'[Besoin concaténé format date]); DESC)
    )
)

However when I try to add the state as legend, I get this:

Categorized running total

I think that the problem is that Power BI calculates the running total on each category. In a way that if after a certain date there is no piece in a given category the category is just not displayed anymore.

I am surprised I couldn't find any help on this anywhere as this can be very easily done on Excel.

I tried "show all the elements without data" without success. I am now looking for a DAX formula to achieve that visualisation. Any idea on the question ?

My data looks like this :

data

Thank you in advance,

Upvotes: 2

Views: 2229

Answers (1)

Olly
Olly

Reputation: 7891

Create a separate Date table - you could simply use 

=CALENDARAUTO()

Create a relationship between 'Date'[Date] and 'Table1'[Besoin Concatenee].

Now create a measure like:

Cumulative Parts = 
VAR DateCurrent = MAX ( 'Date'[Date] )
RETURN
    CALCULATE ( 
        COUNTA ( Table1[Part Number] ),
        FILTER ( 
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= DateCurrent
        )
    )

Now create your Stacked Column chart with

'Date'[Date] in Axis

'Table1'[Status] in Legend

[Cumulative Parts] in Value.

Here's an example PBIX file, with some mocked up data: https://pwrbi.com/so_55811631/

enter image description here

Upvotes: 3

Related Questions