Percentual share change over time period

[Paddhof1984]

Hello dear MS Power BI Community:

I need your help with following issue:

I do have a table which contains the turnover for specific products. This table is linked to a table which groups those products with a product group. Now what I need is to calculate the turnover share of the product group to the total turnover of the specific year, f.e. 2016 and then 2017. Where I struggle now is: how can I calculate their yearly procentual share to total turnover of the year and then to calculate, how the procentual share changed over the year for each product group?

As far as I get is that I can show how the group share is to the years total turnover. Which measure do I have to create for this? Or is there any other possibility with any if else measures, where I can calculate this?

[v-yuezhe-msft · Accepted solution]

@Paddhof1984,

I make a test in the sample PBIX file you shared to me. You can create the following measure in your RE2016 table.

GT 2016 = CALCULATE(SUM(RE2016[TO2016]))/(CALCULATE(SUM(RE2016[TO2016]),'groups renamed'[Group]="Group 0+"||'groups renamed'[Group]="Group 1"||'groups renamed'[Group]="Group 1/2/3"||'groups renamed'[Group]="Group 2/3"||'groups renamed'[Group]="Others"))

Then create the following measures in your RE2017act table.

GT 2017 = CALCULATE(SUM(RE2017act[TO2017]))/(CALCULATE(SUM(RE2017act[TO2017]),'groups renamed'[Group]="Group 0+"||'groups renamed'[Group]="Group 1"||'groups renamed'[Group]="Group 1/2/3"||'groups renamed'[Group]="Group 2/3"||'groups renamed'[Group]="Others"))

2017 VS 2016 =IF( [GT 2017]<[GT 2016] ,([GT 2017]/[GT 2016])*-1, [GT 2017]/[GT 2016])

Regards,

[v-yuezhe-msft · Accepted solution]

@Paddhof1984,

Please check if the following DAX returns your expected result.

2017 vs 2016 = ([GT 2017]-[GT 2016])/[GT 2016]

Regards,

[Greg_Deckler]

I may not be understanding entirely, but essentially what it sounds like is that you want % share of total turnover that respects date filter, something like:

Measure = SUM([Turnover]) / CALCULATE(SUM([Turnover]),ALLEXCEPT([Date]))

Basically, respect the Date filter when caculating the SUM of all of the turnover so that you can plot it on a graph with a month or date.

[Paddhof1984]

@Greg_Deckler

Thanks for your support. I am sorry, but it's not exactly what I need.

What I need is following:

Turnover 2016:

Turnover total:       1000$ = 100%

Turnover Group A:   200$ =   20%

Turnover Group B:   400$ =   40%

Turnover Group C:   300$ =   30%

Turnover Group 😧   100$ =   10%

These values are from my table RE2016 and the turnover per Group is located in the Column RE2016[TO2016].

Turnover 2017:

Turnover total:       2000$ = 100%

Turnover Group A:   800$ =   40%

Turnover Group B:   400$ =   20%

Turnover Group C:   200$ =   10%

Turnover Group 😧   600$ =   30%

These values are from my table RE2017act and the turnover per Group is located in the Column RE2017act[TO2017].

Now what I need is the to see how the percentage share of every single group performed from 2016 - 2017.

Group A 2016 = 20% compared to Group A 2017 = 40%

So in this case Group A's percentage share increased from 2016 to 2017 over 100% .

Group B's percentage share decreased from 2016 to 2017 -50%.

[v-yuezhe-msft · Accepted solution]

@Paddhof1984,

I make a test in the sample PBIX file you shared to me. You can create the following measure in your RE2016 table.

GT 2016 = CALCULATE(SUM(RE2016[TO2016]))/(CALCULATE(SUM(RE2016[TO2016]),'groups renamed'[Group]="Group 0+"||'groups renamed'[Group]="Group 1"||'groups renamed'[Group]="Group 1/2/3"||'groups renamed'[Group]="Group 2/3"||'groups renamed'[Group]="Others"))

Then create the following measures in your RE2017act table.

GT 2017 = CALCULATE(SUM(RE2017act[TO2017]))/(CALCULATE(SUM(RE2017act[TO2017]),'groups renamed'[Group]="Group 0+"||'groups renamed'[Group]="Group 1"||'groups renamed'[Group]="Group 1/2/3"||'groups renamed'[Group]="Group 2/3"||'groups renamed'[Group]="Others"))

2017 VS 2016 =IF( [GT 2017]<[GT 2016] ,([GT 2017]/[GT 2016])*-1, [GT 2017]/[GT 2016])

Regards,

[Paddhof1984]

@v-yuezhe-msft

much thanks for your support! Thanks a lot, the measures created in the specific tables do helb me a lot.

But I am struggling with the final measure, with which I want to visualize the percentual change over a year.

Your measure shows the total change, but what I can't get to work is like subtract 100% of the result, so that the true percentage change is shown.

2017 VS 2016 =IF( [GT 2017]<[GT 2016] ,([GT 2017]/[GT 2016])*-1, [GT 2017]/[GT 2016])

I already tried something like: 2017 VS 2016 = IF(( [GT 2017]<[GT 2016] |([GT 2017]/[GT 2016])| [GT 2017]/[GT 2016])-100%), but that doesn't work. Any idea how I can substract the 100% from your measurement, so that Group 0+ has an positive percentage change also as the group others, wheras Group 1, 1/2/3 and 2/3 have a negative percentage change.

[v-yuezhe-msft · Accepted solution]

@Paddhof1984,

Please check if the following DAX returns your expected result.

2017 vs 2016 = ([GT 2017]-[GT 2016])/[GT 2016]

Regards,

[Paddhof1984]

@v-yuezhe-msft

Your measure almost shows me the right values, I made a small change to it:

2017 VS 2016 = (([GT 2017]-[GT 2016])*100)/[GT 2016]

The issue with your measure is, that if I want to show the values of your calculation as percentage of the total value, Power BI show some really weird long numbers which don't make any sense.

With adding the *100 to your measure, I exactly get the values of percentage shown which I need.

[v-yuezhe-msft · Accepted solution]

@Paddhof1984,

You don't need to add *100. Just turn on percentage format as shown in the screenshot below.



Regards,