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.

     

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
    • Mohammad_Refaei's avatar
      Mohammad_Refaei
      Solution Specialist
      5 years ago

      I have a couple of questions:

      What is your table structure?

      What is the desired output? 

      What do you mean with line item?... do you mean each table row? or aggregated GL?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi,

         

        I have two tables mainly:

         

        1) invoice header level, where each line is each invoice

        2) invoice line item level, where each line is each line item within each invoice, so you can imagine this table to be way bigger.

         

        Relationship between the 2 tables have been setup to link via invoice number.

         

        Table 2) is what I am working on, but that table only has line item value at local currency. I need to convert to USD but this table doesn't have the exchange rate. Exchange rate only exists in Table 1). Hence I wrote that DAX measure to bring in the exchange rate for the corresponding invoice.

         

        I do not need to summarize any columns of data as I want to display it as is. Hope this explains.