Forum Discussion
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 with this two signs ($,$k)
For First Measure/calculation, see the below-expected answer
($,$k (Sum will be $10+$20+$1000+$1000 )) =2030 (a full integer is okay )
For second Measure/calculation, see the below-expected answer
(HH:MM(01:05+02:05))=03:10HH:MM
For third Measure/calculation, see the below-expected answer
(%)(50%+10%)=60%
Below is dummy data with the above-expected answers.
Values SignType
10 $
20 $
1000 $k
2000 $k
01:05:00 hh: mm
02:05:00 hh:mm
50 %
10 %
Thanks in advance
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.
4 Replies
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.
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
MYDATASTORY , you can not sum up the variable kind of data. check for each data and sum it up. create some calculated columns and sperate these
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
