Choose aggregation for table Columns

[azertyuiop123]

Hello guys,

I am new in Power Bi and I would like to create a table in which we would have measures (turnover, expenses ... etc) whose level of aggregation can be chosen, maybe in a segment, by the person consulting my report.

For example :

We have this data table, 

Col1      |    Col2       |   Measure

A           |    a            |    1

A           |    b            |    2

B           |    a            |    3

B           |    B            |    4

and a segment with the values "Col1" and "Col2". If "Col1" is selected, the table would be :

Col1      |   Measure (sum)

A           |    3

B           |    7

If "Col2" is selected, the table would be :

 Col2       |   Measure (sum)

 a            |    4

 b            |    6

I don't know if it is very clear but I didn't find any subjets that come close to it.

Can someone tell me if this is possible?

Thanks.

Emilie

[amitchandak]

With your example, it seems like you want to group data by various parameters/fields by aggregating some field/measure.

That is what happens in most of the visuals.  You just need drag visual to correct position.

[azertyuiop123]

@amitchandak 

[amitchandak]

Refer , if these can help

https://community.powerbi.com/t5/Desktop/Dynamic-Column-Based-on-Slicer-Selection/td-p/81126

https://community.powerbi.com/t5/Desktop/Add-Remove-columns-to-table-based-on-slicer-selection/td-p/...

https://community.powerbi.com/t5/Desktop/Select-columns-in-table-dynamically-based-on-Slicer/td-p/41...

[azertyuiop123]

Hello @amitchandak 

Thank you for your response, but it is not what i am looking for :'( unfortunatly, non of these subjects help me for what i want to do, maybe it's impossible on Power BI.

I always available if someone has an advice.

Emilie

[azertyuiop123]

Hello,

Thank you for your response, it is not exactly what I want. I would like users of my power bi in power bi server to choose the level of aggregation they want. Lika that : (I used 2 data tables separately for the segments et the tables)

If a user chooses non of the columns, the measure will be totally aggregated (sum for example). If he chooses col1 or col2, it will be semi aggregated (sum by col1 or col2 respectively for example). If he chooses col1 and col2, it will be totally disaggregated.

Can you tell me if it is possible?

Thank you very much.

Emilie

[azertyuiop123]

Hello,

I found a "solution". I think it is not at all good practices but it is the only way I found.

I duplicated the rows like this :

Col1	Col2	measure
A	d	1
A	e	2
B	d	3
B	e	4
C	d	5
C	e	6

 ->

Col1	Col2	chosencol1	chosencol2	measure
A	d	col1	col2	1
A	d	col2	col1	1
A	e	col1	col2	2
A	e	col2	col1	2
B	d	col1	col2	3
B	d	col2	col1	3
B	e	col1	col2	4
B	e	col2	col1	4
C	d	col1	col2	5
C	d	col2	col1	5
C	e	col1	col2	6
C	e	col2	col1	6
A	d	col1	col1	1
A	d	col2	col2	1
A	e	col1	col1	2
A	e	col2	col2	2
B	d	col1	col1	3
B	d	col2	col2	3
B	e	col1	col1	4
B	e	col2	col2	4
C	d	col1	col1	5
C	d	col2	col2	5
C	e	col1	col1	6
C	e	col2	col2	6

then I put chosencol1 and chosencol2 in 2 segments, I created 3 columns:

* Colonne1 = if(Table3[chosencol1]="col1", Table3[Col1], if(Table3[chosencol1]="col2", Table3[Col2], BLANK()))

* Colonne2 = if(Table3[chosencol2]="col1", Table3[Col1], if(Table3[chosencol2]="col2", Table3[Col2], BLANK()))

* colonnetest = CALCULATE(MAX(Table3[measure]), ALLEXCEPT(Table3, Table3[Col1], Table3[Col2]))

then I created a table with the 3 new columns to obtain this :

Does someone know another way (more tidy) to do it?

Thank you so much. 

Emilie