Dynamically add Measures in Pivot based on data

[rneelakantam]

Hi All,

I have my data as below:

LOAD * INLINE [
Field1, Field2, Field3, Sales,Year
AAA, CCC, GGG, 10,2000
AAA, CCC, HHH, 20,2001
AAA, DDD, JJJ, 30,2005
AAA, DDD, KKK, 40,2006
BBB, EEE, LLL, 50,2007
BBB, EEE, MMM, 60,2008
BBB, FFF, NNN, 70,2000
BBB, FFF, PPP, 80,2001
];

Requirement:

1) I need to create a Pivot which would have Year as my dimension (ROWS) and one new dimension (which consists of all Field1,Field2 & Field3 values) as COLUMNS

2) Also I want to create a dynamic logic such that if there is any new value added in any of the Fields( i.e Field1,2,3) It should populate the sum(Sales) directly without making any script changes.

[lironbaram]

hi 

your first point is not clear 

you could create a table like this 

Table1:

LOAD * INLINE [
Field1, Field2, Field3, Sales,Year
AAA, CCC, GGG, 10,2000
AAA, CCC, HHH, 20,2001
AAA, DDD, JJJ, 30,2005
AAA, DDD, KKK, 40,2006
BBB, EEE, LLL, 50,2007
BBB, EEE, MMM, 60,2008
BBB, FFF, NNN, 70,2000
BBB, FFF, PPP, 80,2001
];

Table2:

load Field1 as NewField,

          Sales,

          Year

resident Table1;

load Field2 as NewField,

          Sales,

          Year

resident Table1;

load Field3 as NewField,

          Sales,

          Year

resident Table1;

drop table Table1;

this will create the dimension you look for but it will also multiply the sales total by 3 so you need to add some logic how to split the sales between the values in fields : Field1-3 

this script also answer your second point because any new value will automatically added to the table and the corresponding pivot table 

[rneelakantam]

Hi Lironbaram,

Thanks for your reply.

In the sample data which i provided has only few columns where as in my original data we have 50 columns and 200 Million records. If i concatenate and create a new dimension the Sales value would be multiplied. Is there any work around to handle this.

[lironbaram]

hi 

can you explain why you want to convert the 50 columns to one field 

what is the business logic behind it?

[rneelakantam]

Hi,

I don't want to convert all 50 but i want to convert 8 columns to 1 column.Rest would remain as it is.

[Abhinav45]

Dynamic measures is one type of disconnected slicer technique. The other two techniques are dynamic parameters (made much easier with the release of variables in Power BI) and dynamic attributes. This last technique, dynamic attributes, hasn’t gotten much attention, so let’s fix that!

Food Processors India 2019

[rneelakantam]

Hi All,

Any solution for this scenario