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

The measure has to be calculated. The "Don't summarize" is available only for columns.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    Thank you for your reply.

     

    Yes I know "Dont' summarize" only applies to columns, that's why this post is asking for how to do it for measures. As the columns don't give what I want, I need to create a measure which links to another table.

     

    Are you saying there is no solution in this case?

     

    Thanks,
    Jiawei

    • 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