I need to add a row with a measure in a matrix table

[Anonymous]

Any body could give and advice with this issue?: Thanks in advance for your help!

LINK): https://www.dropbox.com/s/ypxv1ift10e5v8q/BI%20VISITAS%20DEMO%20QUERY.pbix?dl=0

[jdbuchanan71]

Hello @Anonymous 
You can add a measure to calculate the Average total by month.  On an individual month the amount will be the same as the sum of the total but on the total it will be the average of those values.

Avg Total = 
AVERAGEX(VALUES(PBI[Fecha]),CALCULATE(SUM(PBI[Total])))

[Anonymous]

and 1 more thing @jdbuchanan71 ...is there a way to make a conditional formating over the "total" you create?

[jdbuchanan71 · Accepted solution]

For the average to consider all (even blank) months you can do this:

Avg Total 2 = 
VAR Months =
    COUNTROWS ( ALLSELECTED ( PBI[Fecha] ) )
RETURN
    IF (
        ISINSCOPE ( PBI[Fecha] ),
        SUM ( PBI[Total] ),
        DIVIDE ( SUM ( PBI[Total] ), Months )
    )

As far as conditional formatting the total, that is not currently possible.  Conditional formats don't apply to totals or subtotals.

[Anonymous]

excelent!!! any advice to learn DAX as a pro as you?

[jdbuchanan71]

A good place to start is with the Introducing DAX video course from SQLBI. 

Also, there are a couple of YouTube channels that release guides and tutorials on a regular bases.

• Enterprise DNA
• Curbal
• PowerBIPro

And if you really want to get into how DAX works take a look at the book The Definitive Guide to DAX also from the guys at SQLBI.

[Anonymous]

MPP DEMO GLOBAL = (SUM(PBI[Total])/SUM('BU HC'[HC]))/(DISTINCTCOUNTNOBLANK(PBI[Fecha]))

Thats the formula I use to get the number I need.... with that formula, how would be to make it work like the one you sent before to use it in the matrix table?

[Anonymous]

Hi @jdbuchanan71 Thank you so much for your help but id like to ask you 1 more thing... how it would be your formula if I want to divide the total by the counting of every date and iclude a filter in it?

Can you helped me with that ?