Forum Discussion
Incorrect Subtotal in Measure - Two Tables Relationship (Many to Many)
Dear All,
I have been struggling with subtotal grouped by date of table Sheet1 and almost applied all the solutions available in this forum to my problem but failed. Here is the situation.
Relationship between Table: OBs and Sheet1 is Many-to-Many
#1 subprinters = IF(HASONEVALUE(Sheet1[Ship Date/Replan Ship Date].[Month]), [#1 Sub_Printer], SUMX(SUMMARIZE(Sheet1, Sheet1[Ship Date/Replan Ship Date].[Month]), [#1 Sub_Printer]))#1 Sub_Printer = DIVIDE(AVERAGE('OBs'[SMV]) * SUM('Sheet1'[Qty]), 480*25*2*0.65)
Filters: It is not filtering department title. I have applied filter by "Printing" but looks like it is also taking others available in department title.
Best Regards.
biengineer , Try like this with column used in table
#1 Sub_Printer = Sumx(Summarize('Sheet1', Date[Year], 'Sheet1'[Art]), calculate( DIVIDE(AVERAGE('OBs'[SMV]) * SUM('Sheet1'[Qty]), 480*25*2*0.65) ))
3 Replies
biengineer , Try like this with column used in table
#1 Sub_Printer = Sumx(Summarize('Sheet1', Date[Year], 'Sheet1'[Art]), calculate( DIVIDE(AVERAGE('OBs'[SMV]) * SUM('Sheet1'[Qty]), 480*25*2*0.65) ))
Thank you so much for the close one.
#1 Sub_Printer = SUMX(SUMMARIZE(Sheet1, Sheet1[Ship Date/Replan Ship Date].[Date], Sheet1[Art]), Calculate(DIVIDE(AVERAGE('OBs'[SMV]) * SUM('Sheet1'[Qty]), 480*25*2*0.65)))The results are quite close, now its the difference of 0.01 throughout.
Ah looks like the results are okay, its due to rounding off.
