cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryan1982
Helper I
Helper I

incorrect sub-category (row) value?

 

While creating a matrix, I dragged "C_B_Date" in MTG table (highlighted in GREEN) to column and added two attributes in rows ("Drop or Active" in ALL_OD table & "Rolling_ttl_MTG" in MTG table, hilighted in BLUE)  

 

In "Rolling_ttl_MTG", there are four values: Group 1,2,3,and 4

 

In "Drop or Active", there are two values: Active and Drop. 

 

Thre relationships between tables are as the 1st. screenshot. 

 

In the 2nd screenshot, even the summation of Group 1,2,3,and 4 are correct (i.e. equal to grand total), but the sum of sub-row values (Active and Drop) in each year are NOT equal to correponding Group 1,2,3,and 4 in each year. For example, in 2015, the number of active and drop added up far less than the number of group 1.

 

Data samples for tables are as 3rd. screenshot.

 

screenshot 1screenshot 1screenshot 2screenshot 2screenshot 3screenshot 3

 

 

 

 

Many thanks!

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @ryan1982 

when i add columns in the matrix visual, it shows and error "can't detemine the relationship between fields".

If i create a table crossjoining two columns and create a measure, it shows as below

Measure = IF(ISINSCOPE('Table'[rolling_ttl_mtg]),IF(ISINSCOPE('Table'[drop]),"each value","sub sum"),"grandtoatl")

1.png

If you doesn't meet the error, your could try the measure i create above and make some changes to apply to your scenario.

 

Additionally, It seems ALL_OD table and MTG table should have ID &Date to detemine their relationship instead of only Date field.

 

Best Regards

Maggie

 

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

Thank you Maggie

 

I will try your solution and report back.

 

How can I determine the relationship both based on date and master_customer_ID?

 

 

Hi @ryan1982 

You can create column like this in two table, then create a relationship between two tables based on the two columns.

column=[id]&[date]

from your screenshot, it seems MTG table doesn't have many columns, you may open edit queries to merge ALL_OD table and MTG table based on [id]&[date] column.

 

Best Regards

Maggie

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.