Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Aggregations on repeated data:

Hi,

 

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

 

Att1Att2Att3Att4M1M2M3M4Att5
apqp10020010025A1
apqp10020010025B1
crei30010014040C1
crei30010014040D1

 

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:

 

Att1Att2Att3Att4M1M2M3
apqp100200100
crei300100140
Totals   400300240

 

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

General Comment

3 Replies

  • Pavlous's avatar
    Pavlous
    Advocate II
    8 years ago

    Hi Anonymous,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Pavlous
        Advocate II
        8 years ago

        Hi Anonymous,

         

        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.