Calculate percentage change of sum of categories

[jomikk]

Hi, I have a table with main categories and many "slicer" columns. The main category is divided into eight different categories.

I have data from 2015, 2016 and 2017. And I want to calculate percentage increase from 2015 to 2017 and 2016 to 2017. But due to these many slicer variables, I can have some percentage increases up to like 20 000 %, but the real (total sum) increase is only around 150% for this category.

I can't use average, since this big percentage increases totally dominate the average, and I get an average of like 10 000% increase.

So, how can I sum up all the values belonging to each category, and then take the percentage increase? Kind of like Groupby the categories, but without having to make a totally new table or anything

EDIT: The optimal solution would be something that considered the slicers when summing up. So when I change my slicer filter, then it would sum the categories while considering the slicers

Hope this was understandable,

Best regards

[Anonymous]

Try something like:

SumPerCategory =
SUMX(
         KEEPFILTERS(Table[Category]);
         SUM(Table[Value]
)

Then create a measures to compare over years

[jomikk]

Here is an example of how the table looks like. Have changed the names and numbers obviously. The letters in the slicer is just to show that these columns are text columns. And as we can see on the values, it can contain blank rows sometimes, but I suppose that won't pose any problems for summing numbers up.

So how can I create a measure/column/function that sums each category, while considering which slicers is active in the report? Is this even possible? (I guess it is possible since the charts already sum up the values for each year based on the slicers!)

[Anonymous]

Please share your report with the slicers and explain what the result is you are looking for.

Summing values based on selected slicers is default behaviour in Power BI, so not really sure what you are looking for.

[jomikk]

Can't share report because of NDA on data.

Yes, but I want to use the summed values (based on selected slicers) to calculate the percentage change for each year.

How can I access the summed values that are based on the selected slicers from the chart?

[Anonymous · Accepted solution]

Because you have a column for each year I would first suggest to unpivot those rows so you can use these calculations based on slicers but for now try:

New measures:

2015 = SUM(Table[2015])

2016 = SUM(Table[2016])

2017 = SUM(Table[2017])

Percentage change = DIVIDE([2017]-[2016];2016)

[jomikk]

It worked! Thank you!

[jomikk]

Btw, what do you mean by unpivoting the rows?