Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

why my measure not summarize?

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

Need Help

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago


      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.