Please I have a problem, first I have a table with repeated values like this
|Names||number of transactions|
I want to get the sum of transactions considering that there are repeated record. There dupplicates are the same and outcome of a json column.
I used the following DAX functions to get the sum which seem correct.
when I visualized the data using table or matrix in power bi here is what I have
|Name||number of transactions|
The total is correct but the values are not adding up.
Please I need help.
I want to say thank you so much everyone for your support and I also very sorry, the problem was from me,
I was basing my calculations on the dimension table.
I had to do it from the fact table and I am good now.
Hi @dekings ,
Plaease try below steps:
1. below is my test table
2. create measure with below dax formula
Measure = VAR cur_name = SELECTEDVALUE ( 'Table'[Names] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Names] = cur_name ) VAR tmp1 = CALCULATETABLE ( VALUES ( 'Table'[number of transactions] ), tmp ) VAR _a = SUMX ( tmp1, [number of transactions] ) RETURN _a
Measure 2 = SUMX(VALUES('Table'[Names]),[Measure])
3. add a table visual with field and measure
Please refer the attached .pbix file.
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.
Thank you @FreemanZ for your response,
the answer I got add all the rows but I need it to take only one value per name and add all the values such that when I visualize it I will get the second table in my question
Hope the following helps: However, the best practice would be to remove duplicates
Thank you for your suggestions.
So I tried using the dax you shared, it returned me to the answer I have getting.
Thank you for your responce.
So this duplicate is not available in Power query level.
I created it using related function in dax from the dimension table.
Hey @dekings ,
Why don't you remove the duplicated rows in the Power Query Editor? is it not possible for you?
Check out the changes to the Power BI Community announced at Build.
Find out more about the May 2023 update.
Visit our Data Stories Gallery and give kudos to your favorite Data Stories.