Aggregations on repeated data:

[manalla]

Hi,

I have a sample data set like below (replicated to my scenario):

Att1	Att2	Att3	Att4	M1	M2	M3	M4	Att5
a	p	q	p	100	200	100	25	A1
a	p	q	p	100	200	100	25	B1
c	r	e	i	300	100	140	40	C1
c	r	e	i	300	100	140	40	D1

In the above table, first two rows and 3rd and 4th row looks duplicated till M4 column, but when seen with Att5 they are unique.

Now the requirement is when Att5 is dragged to table visual I will not show SUM for measures (M1,M2,M3 and M4). It will be just a informative table with out any aggregations.

But when Att5 is not used in the visual, I would like to see the below result:

Att1	Att2	Att3	Att4	M1	M2	M3
a	p	q	p	100	200	100
c	r	e	i	300	100	140
Totals				400	300	240

How can we achieve this sort of rollups or aggregations in Power BI. I have lot many measure columns and columns like Att5 when joined with multiple datasets.

Please suggest.

Thanks

[Pavlous]

Hi @manalla,

It should work automatically within Matrix visualisation if you use as values only those columns you need. 

[manalla]

Hi @Pavlous

Thanks for reply.

I did try with Matrix and it works. But User is not happy with drill down to levels, when multiple attributes are added to rows, it becomes cumbersome to see data with all levels drilled down.

There are more than 15 text attributes that are viewed on measures in my case.

Any DAX can help to acheive this at Table visual. Matrix is not helping interms of visual appeal in my context.

Thanks

[Pavlous]

Hi @manalla,

Allrighty, so you would need to make custom column with condition if the "column" is for example Attr5 then its 1 else 0.

Then you can use this column as switcher in filter visualisation to show and hide the "column" from the context. It should automatically update the result to what you would like to show.

EDIT: If you would like it to make it more user friendly then only 1 and 0 then you can make Ref table with e.g. Type and Value when Type column will have Displayed and Hidden and Value for Displayed 1 and Hidden 0.