cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ContabilidadBI Helper III
Helper III

percent of grand total of a Matrix, as another value in the leyend of a line chart

Hello guys,

 

I have wrote a measure that calculates the sum of sales in the values area of a matriz but the average in the grand totals, the matrix looks like this:

 Captura.PNG

 I would like to show a line chart with years on the leyend, and also another line for the average (which is the value that the grand totals show if I use a Matrix). It would be something like this:

 

Captura1.PNG

The only thing missing in that chart is another line for the running average (which is the value of the grand total of the measure). I hope you can understand what I mean. Is that something possible to achieve?

 

Thank you very much,

 

Víctor

4 REPLIES 4
Super User IV
Super User IV

Re: percent of grand total of a Matrix, as another value in the leyend of a line chart

What you probably need to do is to create a new measure that uses an ALLEXCEPT filter. Like:

 

m_MyMeasure = CALCULATE(SUM(Table[Column]),ALLEXCEPT(Table,Table[Mes]))

That's essentially what is going on in the totals row. 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ContabilidadBI Helper III
Helper III

Re: percent of grand total of a Matrix, as another value in the leyend of a line chart

Hi,

 

Thanks for your answer, but I think I didn't explain myself correctly. What I am trying to do is to replicate and understand this blog post by Javier Guillen: https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/

 

In the final part of the post you can see what I want. I am sure you will understand my problem better.

 

Thank you very much.

Microsoft
Microsoft

Re: percent of grand total of a Matrix, as another value in the leyend of a line chart

Hi @ContabilidadBI,

 

From the link you provided, it suggest generate a running average via dividing running total by year count. Have you tried that? Does it work in your scenario?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ContabilidadBI Helper III
Helper III

Re: percent of grand total of a Matrix, as another value in the leyend of a line chart

Hi Yuliana thanks for your answer,

 

Yes I did it and I can have a matrix with the running average in the grand totals, what I would like to do is to represent the sales of each year and the average sales in the same line chart, just like he does at the very end of the post.

 

I can't achieve that because if I put Calendar[year] in the leyend of the line chart, I have only a line for each year, but I don`t have the grand total (which is the actual running average).

 

Thanks

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors