Need help on Matrix visual DAX logic

[SarathB2]

Hello Experts,

I'm trying one output in Matrix visual that expecting the monthly wise count  and when its come to Total in Matrix i need the average count over no. of months (non-blank) in the total. 

Please assist,

somthing like, instead of 28/12  need 28/6 (non-blank) in the Total area....

Jan-21

Feb-21

Mar-21

Apr-21

May-21

Jun-21

Jul-21

Aug-21

Sep-21

Oct-21

Nov-21

Dec-21

Total

Average(Count)

4

6

4

8

2

4

Average(28/6)

In same scenorio, can we show like this?

Jan-21

Feb-21

Mar-21

Apr-21

May-21

Jun-21

Jul-21

Aug-21

Sep-21

Oct-21

Nov-21

Dec-21

Total

Average Count

4

6

4

8

2

4

Total Count ,Instead of average here

Thanks in advance.

Regards,

Sarath.

[v-easonf-msft · Accepted solution]

Hi, @SarathB2 

You can try  'HASONEVALUE' to change the matrix row total.

Count_month non_blank = 
IF(
    NOT(HASONEVALUE('Calendar'[Date].[Month])),
    CALCULATE(
        DISTINCTCOUNT('Calendar'[Date].[Month]),
        FILTER(
            'Calendar',
            'Calendar'[Date] in DISTINCT('Table'[Date])
        )
    )
)

Average（Count） = 
IF (
    HASONEVALUE ( 'Calendar'[Date].[Month] ),
    COUNTROWS ( 'Table' ),
    COUNTROWS ( 'Table' ) / [Count_month non_blank]
)

If it doen't meet your requirement ,please share your sample file or relevant screenshots(formulas,tables) 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SarathB2]

Hey @v-easonf-msft ,
Thanks for your efforts for the solutions .
Great solution

..

[amitchandak]

@SarathB2 , if you Sum measure and you want Avg above month year

AverageX(Values('Date'[Month Year]), [Sum])

If you have Avg measure and you need sum above month level
Sumx(Values('Date'[Month Year]), [Avg])