Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.