Forum Discussion

rodfernandez's avatar
rodfernandez
Helper I
7 years ago
Solved

cumulative bar graph

I have the following Graph 

but I want to create a cumulative graph of the previous graph.
I create the following code

Acomulado RCA = 
CALCULATE(
	COUNTA('Reporte'[Etiqueta]);
	FILTER(
		CALCULATETABLE(
			SUMMARIZE('Reporte'; 'Reporte'[Mes]; 'Reporte'[Mes Name]);
			ALLSELECTED('Reporte')
		);
		ISONORAFTER(
			'Reporte'[Mes]; MAX('Reporte'[Mes]); DESC;
			'Reporte'[Mes Name]; MAX('Reporte'[Mes Name]); DESC
		)   
	)
)

that produce the following bar graph 

 



This is more or less what I wanted but I need that each element persist in time, for example for January in the first graph you have 1 yellow so in the cumulative graph you should have 1 yellow, so is fine. In February you have 1 red in the normal graph so in the cumulative graph you should have 1 yellow and 1 red, .... For March you should have 1 yellow, 1 red and 1 green, in May 1 yellow, 1 red and 3 green,....and so on 
what should I change in my code make each color persist over time?

 

 

  • hi, rodfernandez 

    You could try this way:

    Step1:

    Create a fact Mes table, then create the relationship with report table by Mes

    Step2:

    Use this formula to add a measure

    Acomulado RCA = 
    CALCULATE (
        COUNTA ( 'Reporte'[Etiqueta] ),
        FILTER (
            ALLSELECTED ( Mes ),
            ISONORAFTER ( 'Mes'[Mes], MAX ( 'Mes'[Mes] ), DESC )
        )
    )

    Result:

     

    and here is my sample pbix file, please try it.

     

    Best Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support

    hi, rodfernandez 

    You could try this way:

    Step1:

    Create a fact Mes table, then create the relationship with report table by Mes

    Step2:

    Use this formula to add a measure

    Acomulado RCA = 
    CALCULATE (
        COUNTA ( 'Reporte'[Etiqueta] ),
        FILTER (
            ALLSELECTED ( Mes ),
            ISONORAFTER ( 'Mes'[Mes], MAX ( 'Mes'[Mes] ), DESC )
        )
    )

    Result:

     

    and here is my sample pbix file, please try it.

     

    Best Regards,

    Lin

    • Amin_Adham's avatar
      Amin_Adham
      Regular Visitor

      Hi Lin.
      Thanks for the advice. it really helped me as well having the same issue.

      can you please explain me briefly about the table creation?
      why did you create a table and how did it help?

  • Amin_Adham's avatar
    Amin_Adham
    Regular Visitor

    Hi Lin.
    Thanks for the advice. it really helped me as well having the same issue.

    can you please explain me briefly about the table creation?
    why did you create a table and how did it help?