Forum Discussion

MYDATASTORY's avatar
MYDATASTORY
Resolver I
6 years ago
Solved

How to sum different values Separately which are Text type,timestamp,% and $ (money) in a column

Hi,  I have Dummy data below  which has two columns (Values  and SignType), and I want to calculate the sum of each sign type as per below-expected answer, the first calculation we're summing values...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi MYDATASTORY ,

     

    Sorry for that Time and Whole Number are different types, so they cannot be added in the same column.

     

    We find a way to get the result you can refer it.

     

    1. Copy two tables in Power Query and filter each table. One filters not “hh:mm” type, another filters “hh:mm”type.

     

     

     

    2. Then we need to create calculate column in two tables.

     

    Column = 
IF([SignType]="$"||[SignType]="$k",CALCULATE(SUM('Table (2)'[Values]),ALLSELECTED()),CALCULATE(SUM('Table (2)'[Values]),FILTER(ALLSELECTED('Table (2)'),'Table (2)'[SignType]="%")))

     

     

    Column = SUMX(ALLSELECTED('Table (3)'),TIMEVALUE('Table (3)'[Values]))

     

     

    And we need to change the column type to Text.

     

     

    3. At last we can use the UNION function to append two tables, the result like this,

     

    Table 2 = UNION('Table (2)','Table (3)')

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
    BTW, pbix as attached.       

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MYDATASTORY's avatar
MYDATASTORY
Resolver I
6 years ago

Hi , 

I have a  two-column table, Value and ValueSymbol  I want to calculate three measures, below is the expected answer, please note $6+k$4 has different ValueSymbol but I would like to add them together.

 

Below is sample dummy data

 

  • $100+k$1000 =1100   
  • 01:30HH:MM+02:30HH:MM =04:00HH:MM
  • 10%+5%=15%

Value     ValueSymbol  

100            $

1000          k$

01:30        HH: MM

02:30        HH: MM

10            %

5              %

 

Thanks in advance 

v-easonf-msft's avatar
v-easonf-msft
Community Support
6 years ago

Hi ,  MYDATASTORY 

It seems that you have get the solution here.

Please mark the helpful replies or add your reply as Answered to close this thread.
It will help other community members easily find the solution when they get the similar issue.

 

Best Regards,
Community Support Team _ Eason