topic Re: Dynamically add Measures in Pivot based on data in App Development

Dynamically add Measures in Pivot based on data

rneelakantam — Fri, 02 Apr 2021 07:59:45 GMT

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.

Re: Dynamically add Measures in Pivot based on data

lironbaram — Tue, 02 Jul 2019 05:38:05 GMT

your first point is not clear

you could create a table like this

Table1:

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

Re: Dynamically add Measures in Pivot based on data

rneelakantam — Tue, 02 Jul 2019 06:22:09 GMT

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.

Re: Dynamically add Measures in Pivot based on data

lironbaram — Tue, 02 Jul 2019 06:37:13 GMT

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

what is the business logic behind it?

Re: Dynamically add Measures in Pivot based on data

rneelakantam — Tue, 02 Jul 2019 06:40:08 GMT

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.

Re: Dynamically add Measures in Pivot based on data

Abhinav45 — Wed, 03 Jul 2019 05:48:09 GMT

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

Re: Dynamically add Measures in Pivot based on data

rneelakantam — Mon, 08 Jul 2019 11:02:01 GMT

Hi All,

Any solution for this scenario