How to remove summarize function for measures in Table visualization?

[Anonymous]

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!

Thanks,

Jiawei

[Mohammad_Refaei]

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

[Anonymous]

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]

You can control the displayed values by the measure itself.

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

[Anonymous]

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]

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]

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.

[Mohammad_Refaei]

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] )

[Anonymous]

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...

[Mohammad_Refaei · Accepted solution]

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]

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

[Mohammad_Refaei]

Glad the solution is working now.

Don't summarize option is available only for columns not measures.

[Anonymous]

Thanks so much for your help on this!

[Mohammad_Refaei]

My pleasure