Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Value ValueSymbol
100 $
1000 k$
01:30 HH: MM
02:30 HH: MM
10 %
5 %
Thanks in advance
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
@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 ,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |