Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I am new in DAX. trying to get Total Cons by Code. if there is a duplicate Cons (code 10, only sum one of it which 535).
I was using SUMX, SUMMARIZE, MAX. But got wrong result for code 40. it gave me 3 where it is supposed to be 4.
Any help, would be much appracited!
Wanted the result as below.
Result | |
535 | 10 |
4 | 30 |
4 | 40 |
0 | 50 |
4 | 41 |
The data as follows
Cons | charge | Code |
535 | 52.86 | 10 |
535 | -4.55 | 10 |
0 | 12.83 | 10 |
0 | 1.71 | 10 |
4 | 6.56 | 30 |
0 | 12.35 | 30 |
0 | 6.87 | 40 |
3 | 1.56 | 40 |
1 | 1.11 | 40 |
0 | 4.5 | 50 |
4 | 12.4 | 41 |
0 | 6.55 | 41 |
Thanks,
-L
Solved! Go to Solution.
Hi @lwibis ,
Please try below steps:
1. below is my test table
Table:
2. create measure with below dax formula
Measure =
VAR cur_code =
SELECTEDVALUE ( 'Table'[Code] )
VAR tmp =
FILTER ( ALL ( 'Table' ), 'Table'[Code] = cur_code )
VAR tmp1 =
CALCULATETABLE ( VALUES ( 'Table'[Cons] ), tmp )
RETURN
SUMX ( tmp1, [Cons] )
3. add table visual with field and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @lwibis ,
Please try below steps:
1. below is my test table
Table:
2. create measure with below dax formula
Measure =
VAR cur_code =
SELECTEDVALUE ( 'Table'[Code] )
VAR tmp =
FILTER ( ALL ( 'Table' ), 'Table'[Code] = cur_code )
VAR tmp1 =
CALCULATETABLE ( VALUES ( 'Table'[Cons] ), tmp )
RETURN
SUMX ( tmp1, [Cons] )
3. add table visual with field and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @lwibis
not sure about the logic behind. Like why cons 1 and 3 is not included in the result? How comes code 50 for cons 0?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |