why my measure not summarize?

[Anonymous]

hi

I have a simple measure: fx rate *  balance.

I noticed it is not summarized. even if I create  a simple card visualization with this measure only its showed blank:

also when I create a matrix visualization and put 2 simple column: country+measure. each line show what I expected, however in the bottom I did not see the total. (even if I go to the matrix visualization the grand total is On)

why I miss the total and how can I add??

[v-juanli-msft]

Hi @Anonymous 

Could you show me more information?

eg. how do you create the measure?

what is your data? could you show examples?

Best Regards
Maggie

[Anonymous]

Thank you for the questions and help
I reach my oracle data table with direct query function. In this table I have a currency code column, the data table is transaction based so it is very big each line represent 1 transaction with a particular currency. The currency code column is text format 3character.
In the other hand I have an xls input with currency code, rate. The xls input the currency code column also text 3 character.
Using this column I create a lookupvalue formula. To identify the fx rate for each line
LOOKUPVALUE(Sheet1[fx rate],Sheet1[key for fx],SELECTEDVALUE(data table_V[CURRENCY_CODE],99))

In the data table I have credit debit column and create a balance using the following: debit-credit
When I multiple the fx rate measure*balance in each line I have the values that I want but its not summarize.

I also tried to create connection between this 2 table using 1:many connection and be the fx key the unique . with this method I have very big amount and I did not find out why.

Not sure what should I use, but the sumx not working and I don’t have the knowledge how should I be able to create the correct values

so maybe I need to start the begining can you advise what should be the ideal why to create the correct values?

Thank you for the help

[v-juanli-msft]

Hi @Anonymous 

As tested, with Both relationship, we could create rate measures in Table1 and get results as below:

Measure_debit = SUM(Table1[debit])

Measure_rate = MAX(Table2[rate])

debit*rate = [Measure_debit]*[Measure_rate]

The result can summarize by the columns in the table

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

 Thank you very much for your help and the explanation.

well. its basicly working it has summarized values. see the attached print screent. first column is my old that not summarized

measure 3,4,5.6 is following your logics with summarized values. however I expext 81m not 168. so not 100% sure why I have more than a double values. any ides?

thank you 

[amitchandak]

Hope it is created like

sumx(table,fx rate * balance)

[Anonymous]

than you for the answer I tried that and I have the following error:
specified cast is not valid. the exception was raised by the idatareader interface

[amitchandak]

Check data type of both columns. They should be whole number, Decimal or some numeric type. If not. Please go and change it in Data View

[Anonymous]

I checked and every column that participate in the measure is decimal number.

both the fx rata and both the account balance