Matrix - Multiple Rows

[adam_b]

Hi,

Hoping someone can point me in the right direction or what I am missing?

When I have one row in my Matrix the values display correct - however as soon as I have multiple Rows to allow drilldown etc the Values are wrong.

See example below.

1 row in Matrix

Category	Total
Category A	50
Category B	42

Multiple Rows in Matrix

Category	Total
Category A	33
Product 1	15
Product 2	10
Product 3	8
Category B	19
Product 1	13
Product 2

[Anonymous]

Hi @adam_b 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous]

Hi @adam_b 

I think your problem is when you only add category into Matrix Row, you measure shows correct result you want.

(Category A Total = 50, Category B Total = 42)

When you add multiple columns into the Matrix Row (Catehory and Product) , your measure will return to a wrong result.

Is your wrong measure:TotalPrevious?

Please share a sample without sensitive data like what you are dealing with me by your Onedrive for Business.

And you can show me a screenshot of the result you want.

This may make it easier for me to understand your measure.

Best Regards,

Rico Zhou

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

[adam_b]

Hi @Anonymous 

Difficult to share data as its being pulled straight from SQL and for some reason If I make an example data in Excel it seems to work ok?

See below, first screenshot is with just one row - however I've noticed the Spend (Same Period Last Year) still is not correct.

Second screenshot is when I add in multiple rows the previous year value decreases.

This is for periods 01/08/2020 - 31/08/2020 - so the Spend (Same Period Last Year) should be showing around £94k but even with one row it is still incorrect.
If I change the Date Filter to show 01/08/2019 - 31/08/2019 I get £94k but with one row its showing £88k and with 3 rows it shows £32k.

I dont know if its my data, measure or I'm just doing something wrong?

My Measure is 

Spend(Same Period Last Year) = CALCULATE(SUM(Query1_2[LINE_AMOUNT]),SAMEPERIODLASTYEAR('Date'[Date]))

[adam_b]

Wow. Really confused dont know if its because I have a Date Slicer which Syncs between pages, however Just created a standalone date slicer and matrix table and it seems to work...

[Anonymous]

Hi @adam_b 

I think you need to build a unrelated Date table to be your slicer.

If your Date table which you used to build date slicer is related to your Fact table, the time intelligence function may show wrong result due to the filter.

Best Regards,

Rico Zhou

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

[AllisonKennedy]

@adam_b  Can you provide some more info please? Is "Total" a DAX measure? If so, what is the formula and how are the tables related in model view to Category and Product name and totals?

[adam_b]

Probably a better explination, I think I rushed when posting this. TotalNow = CALCULATE(SUM(Data[LINE_AMOUNT])) TotalPrevious = CALCULATE(SUM(Data[LINE_AMOUNT]),SAMEPERIODLASTYEAR('Dates'[Date])) TotalNow works fine as far as I can see however my TotalPrevious is to calculate the same period on the previous year - this works fine when I only have 1 row as soon as I add in multiple rows to have drilldown the TotalPrevious figures are wrong? Took me about 40 minutes to post this as I keep getting a message saying my post has INVALID HTML in the message body...then when trying to re-post it tells me i'm flooding/spamming change the message and back in a loop..