cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dekings
Frequent Visitor

row values not showing the correct result while total shows the correct value

Please I have a problem, first I have a table with repeated values like this

 

Namesnumber of transactions
Kingsley85
Kingsley85
Salome75
Ihemere55
Ihemere55
Kingsley85
Okechukwu20
Salome75
Okechukwu20
Ihemere55
Kingsley85

 

 

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.

max_tansaction = MAX('data'[number of transactions])
then this 
total transaction = SUMX(DISTINCT('data'[Name]), [max_transaction])

when I visualized the data using table or matrix in power bi here is what I have

 

 

Namenumber of transactions
Kingsley233
Okechukwu224
Ihemere232
Salome231
Total235

 

The total is correct but the values are not adding up.
Please I need help.

 

Thamk you

8 REPLIES 8
dekings
Frequent Visitor

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.

v-binbinyu-msft
Community Support
Community Support

Hi @dekings ,

Plaease try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1675734776067.png

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

vbinbinyumsft_1-1675734868740.png

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.

FreemanZ
Community Champion
Community Champion

hi @dekings 

try like:

measure = 
SUMX(
   ADDCOLUMNS(
        VALUES(data[Name]),
        "Sum",
        CALCULATE(SUM(data[number of transactions]))
    ),
    [Sum]
)

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 

Padycosmos
Solution Sage
Solution Sage

Hope the following helps: However, the best practice would be to remove duplicates

Padycosmos_0-1675725555015.png

 

Hello @Padycosmos 

Thank you for your suggestions.

So I tried using the dax you shared, it returned me to the answer I have getting.

No changes.

dekings
Frequent Visitor

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.

LQuedas
Resolver II
Resolver II

Hey @dekings ,

 

Why don't you remove the duplicated rows in the Power Query Editor? is it not possible for you?

LQuedas_0-1675721786338.png

 

LQuedas_1-1675721795873.pngLQuedas_2-1675721834398.png

 

Cheers, LQ

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors