Powerbi group by sum measure in table visual

[nagasaikumarb22]

Hi all,

I require this in table visual,

id         Col 2   col3   col4   sum

1111      1                              3

1111       2                            should not display anything

22222     5                             9

22222     4                             should not display anything 

I used the following measure

Calculate(sum(col2) ,allexcept(id)))

Its showing as below

id         Col 2   col3   col4   sum

1111      1                              3

1111       2                            3

22222     5                             9

22222     4                             9

Pls help me in resolving this

[PaulDBrown · Accepted solution]

Ok, to be able to show all rows, you are going to need to add an index column in Power Query.

I've also created a dimension table for name, address and ID. The mopdel looks like this:

You can then use this measure:

Summary Salary =
VAR _SalaryByID =
    CALCULATE ( SUM ( fTable[Salary paid] ), ALLEXCEPT ( fTable, 'Dim Name'[Id] ) )
VAR _MinIndex =
    CALCULATE ( MIN ( fTable[Index] ), ALLEXCEPT ( fTable, 'Dim Name'[Id] ) )
RETURN
    IF ( MAX ( fTable[Index] ) = _MinIndex, _SalaryByID )

Add the fields & the measure to a table visual, right click on the Index field and select "Show items with no data:

Sample PBIX file attached

[PaulDBrown]

What does the original data look like?

[nagasaikumarb22]

Hi,

I can provide only sample data as client data is confidential from my end.

[PaulDBrown]

Sample data would be perfect

[nagasaikumarb22]

https://docs.google.com/spreadsheets/d/1rB4mWzoPRSfC3_wx8zs8b_r0Y8gz-Do4/edit?usp=sharing&ouid=10601...

[PaulDBrown]

Thansks for the sample. Just to confirm, one of the IDs has more than 1 mobile number, correct? Also, it appears you want to show all the rows, but only the aggregated Salary per ID  on one row: is there any particular criteria to allot the aggragated salary to a row?

[nagasaikumarb22]

Yes we need to display all rows from sample with one aggregated sum.Ignore mistake in mobile number

[PaulDBrown · Accepted solution]

Ok, to be able to show all rows, you are going to need to add an index column in Power Query.

I've also created a dimension table for name, address and ID. The mopdel looks like this:

You can then use this measure:

Summary Salary =
VAR _SalaryByID =
    CALCULATE ( SUM ( fTable[Salary paid] ), ALLEXCEPT ( fTable, 'Dim Name'[Id] ) )
VAR _MinIndex =
    CALCULATE ( MIN ( fTable[Index] ), ALLEXCEPT ( fTable, 'Dim Name'[Id] ) )
RETURN
    IF ( MAX ( fTable[Index] ) = _MinIndex, _SalaryByID )

Add the fields & the measure to a table visual, right click on the Index field and select "Show items with no data:

Sample PBIX file attached

[nagasaikumarb22]

Hi i got your approach,but the problem here my data contains 120 columns,Do we need to summarise repating data or all 120 columns except id

Thanks in advance

[PaulDBrown]

If the other columns are values, then yes, you will need a measure for each

[nagasaikumarb22]

No i mean that if we are having 120 columns in sample table.Do we need to summarise with  120 columns because i am not getting desired output when summarised with few columns? 

[PaulDBrown · Accepted solution]

It depends what you want to "group" the values by. If it's only ID (which is the same for name and mobile number) then all you need is 

ALLEXCEPT(fTable, 'Dim Name'[Id])

If you need to "group" by more columns, you need to include them in the ALLEXCEPT