Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the following measure:
SUMX(SUMMARIZE(Master, Master[Total Revenue]), Master[Total Revenue])
I need to use this measure because my table has some duplicate revenue numbers. For example:
ID | Name | Bill Date | Billed By | Total Revenue |
1001 | ABC INC | 2022-09-01 | John | 120000 |
1001 | ABC INC | 2022-09-01 | Jack | 120000 |
The total revenue for the account 1001 is 120000, thus I cannot use a simple SUM measure.
However, what I have noticed is that when I have two different entries for two different accounts with the same Total Revenue, the Totals in my Matrix doesn't sum both. For example:
ID | Name | Bill Date | Billed By | Total Revenue |
1001 | ABC INC | 2022-09-01 | John | 120000 |
1001 | ABC INC | 2022-09-01 | Jack | 120000 |
1002 | XYZ | 2022-10-05 | James | 8000 |
1003 | ABB | 2022-11-01 | Mark | 120000 |
This is what the matrix shows:
ID | Revenue |
1001 | 120000 |
1002 | 8000 |
1003 | 120000 |
Total | 128000 |
However, in reality, the matrix show show this:
ID | Revenue |
1001 | 120000 |
1002 | 8000 |
1003 | 120000 |
Total | 248000 |
Is there a way to update the measure to still only sum the unique total revenue per ID, but in total include the same revenue amount from different IDs?
Solved! Go to Solution.
Hi @byr10112 ,
You can try this method:
New a measure:
SUM = SUMX(SUMMARIZE('Table','Table'[ID],'Table'[Total Revenue]),[Total Revenue])
The result is:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @byr10112 ,
You can try this method:
New a measure:
SUM = SUMX(SUMMARIZE('Table','Table'[ID],'Table'[Total Revenue]),[Total Revenue])
The result is:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
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 |
---|---|
104 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |