Using Monthly Totals in Matrix in a further measure

[Daveyt69]

Hello

I am hoping someone can help me with this I have spent the last few days, on and off, trying to find a solution to this here and I am assuming there is a simple solution that is escaping me.

I am simply trying to use the monthly column total highlighted in the attached screenshot in new measure.

Sounds simple right…. However, every time I use the following measure

TotalMonthlyPass = CALCULATE (SUM ('Test Event'[TEST_PASS]), ALLSELECTED ('Test Event'))

I get the overall total for all test passes regardless of how the matrix is split by date. I think I follow why I am getting this but I need the total for the month in question only as I plan to use this in future measures.

I know this is probably a simple thing to do but I am at a loss.

Thanks in advance for any help.

Regards

David

[v-eachen-msft · Accepted solution]

Hi @Daveyt69 ,

You need a independent table to be a slicer.

slicer =
SELECTCOLUMNS ( test, "date", test[date] )

And your measure is like the following code:

Measure =
CALCULATE (
    SUM ( 'test'[Pass] ),
    FILTER (
        test,
        test[date] <= MAXX ( ALLSELECTED ( slicer[date] ), slicer[date] )
    )
)

Here is the result.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daveyt69]

Thanks Eads

[Ramyzo]

Greeting David 

To undrestand your problem , when you use that command , it give you the total measures but when u split it with date it keep showing the same Results right ? always show you the total measure ? 

Kind regards 

[Daveyt69]

Hi Ramyzo

Thanks for getting back so quick.

Yes .  Looking at my screen shot when I use the measure it gives me the total for January (73652) plus February  (66459) added together and I assume if I had more months data it would give me the total for every month added together.  What I really need is each month's column total so I can use them in future measures. So for January I need just the 73652 figure and a seperate figure for February and so on as more and more data gets added.  This rep[ort will eventually look  at whatever testing period is needed but I've only got two months data so far.

Cheers

David