Forum Discussion

murali5431's avatar
murali5431
Helper III
5 years ago
Solved

Reprt builder expression for calculation based on distinct values in a column

Hi,   I have a power BI report builder which has duplicated rows (column A) as below. I need to calculate sum of column B based on unique values in column A. Please let me know the report builder e...
expressions
Report Builders
  • Anonymous's avatar
    Anonymous
    5 years ago

    murali5431 
    A simple measure should do:

    Measure = SUMX(SUMMARIZE('Table',[Column A],'Table'[Column B]),[Column B])
     
    And if there are always 2 repeated values, you can also use:
    Measure = SUMX(DISTINCT('Table'[Column B]),[Column B])

     


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.
murali5431's avatar
murali5431
Helper III
5 years ago

HashamNiaz 

Values in ColumnB will always be exactly same for repeted ColumnA members, so we can ignore the scenario of columnB having different values for a value in column A. 

Thanks for checking!

 

Regards,

Muralidhar

HashamNiaz's avatar
HashamNiaz
Solution Sage
5 years ago

Hi !

I would solve this like below;

 

1) Create a new table using DAX;

Go to Modelling Tab -> New Table, provide following DAX

NewTable = SUMMARIZE(Table, Table[ColumnA], Table[ColumnB])


Table is the name of your table, you can replace it with your table name in the formula.

 

Now create a measure to sum column B, use following DAX

NewSum = SUM(NewTable[ColumnB])

 

Hope this will solve your requirement.

 

Regards,

Hasham