Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to remove summarize function for measures in Table visualization?

Hi, I have a Table visualization in my PowerBI but I can't seem to NOT summarize one of the fields which is a measure, is there any way to go about it?   Hope to have some help here!   Than...
Need Help
Tips and Tricks
  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    5 years ago

    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.

     

Mohammad_Refaei's avatar
Mohammad_Refaei
Solution Specialist
5 years ago

You can control the displayed values by the measure itself.

Share more details on your model and the measure you want to modify.

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Hi,
 
Below is the DAX on my measure.
 
LineTotal (USD) = SUM(viewBBeImInvoicePosition_GLReport[LineTotal])*AVERAGE(viewBBeImInvoice_GLReport[ExchangeRate])
 
I need to calculate the USD equivalent on my line item amount, which is currently in local currency, but the FX rate is only available in another table, linked by a unique key, which is the invoice number. Hope this helps!
 
Thanks,
Jiawei