Forum Discussion
How to remove summarize function for measures in Table visualization?
It is not recommended to use bi-directional many-to-many relationships in your model. We will use it but will mark it as inactive. (uncheck Make this relathioship active)
Create the calculated column as follows:
LineTotal USD = [LineTotal] * CALCULATE ( TOPN ( 1, VALUES ( viewBBeImInvoice_GLReport[ExchangeRate] ) ), USERELATIONSHIP ( viewBBeImInvoice_GLReport[InvoiceGuid], viewBBeImInvoicePosition_GLReport[InvoiceGuid] ) )assuming that exchange rate is repeated for the same invoice.
I think the issue is that you are using a measure while it is better to use a calculated column.
Create a calculated column in Table 2 as follows:
LineTotal USD = [LineTotal] * RELATED ( Table1[ExchangeRate] )
Thanks,
I tried using your method but it keeps returning this error:
The column 'viewBBeImInvoice_GLReport[ExchangeRate]' either doesn't exist or doesn't have a relationship to any table available in the current context.
I have created a relationship between both tables, not sure why that error still exists...
Glad the solution is working now.
Don't summarize option is available only for columns not measures.
It is not recommended to use bi-directional many-to-many relationships in your model. We will use it but will mark it as inactive. (uncheck Make this relathioship active)
Create the calculated column as follows:
LineTotal USD = [LineTotal] * CALCULATE ( TOPN ( 1, VALUES ( viewBBeImInvoice_GLReport[ExchangeRate] ) ), USERELATIONSHIP ( viewBBeImInvoice_GLReport[InvoiceGuid], viewBBeImInvoicePosition_GLReport[InvoiceGuid] ) )assuming that exchange rate is repeated for the same invoice.
Hi!
This final solution worked well! I applied and the formula no longer gives any error! And you're right, the exchange rate from the header remains the same at each line item level.
On a side note, I realized the reason why my relationship was Many-to-Many previously, as my invoice header table is not unqiue by invoiceguid, somehow there was some duplicates in there from the system which I don't know how it can happen. Nonetheless, I removed the duplicates and re-mapped the relationship using a One-to-Many, and even the earlier formula (LineTotal USD = [LineTotal] * RELATED ( Table1[ExchangeRate] )) is working now!
With this now as a column, I can now click on "Don't summarize" and it works well! Thanks for your help on this! Really appreciate it!
Last question, does that mean we can't select the "Don't Summarize" option as long as it is a measure instead of column? Is measure meant to be summarized simply based on its definition?
Thanks,
Jiawei
Thanks so much for your help on this!
My pleasure