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.
HashamNiaz's avatar
HashamNiaz
Solution Sage
5 years ago

Hi !

Does the values in ColumnB will always be exactly same for repeted ColumnA members. How you want the calculation to work if you have have PartA with 2 distinct values in ColumnB, 150 & 120. What you want to do in this scenrio.

 

Regards,

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