How to calculate % of Total Revenue with dynamic filters in Power View?

mozartiano

New Member
Joined
Sep 18, 2014
Messages
18
Hello guys,

I am facing a situation with Power View that is driving me nuts :(

The goal is to use a Revenue's table (Faturamento) that has a field named Valor NF (Invoice's Revenue) and calculate what percentage of the total revenue (sum of all VALOR NF for all rows in the table) that single Invoice represents.

Looks simple and I built this simple formula and added it to a calculated column:

=[Valor NF]/sumx(Faturamento;Faturamento[Valor NF])

However, when I insert that column in power view and select filters (only year 2014 for instance), the value is not being recalculated accordingly. So, in a Pivot Table with Customers on the line, the TOTAL starts with 100%, but when I remove year 2014 using a filter in the side bar, the value changes to 40%, and that is not what I want to see. I would like the value to keep 100% and the percentage for each customer to change to a respective value.

In other words, I'd like the sumx function to dynamically recalculate according to the filters, like a PARTITION BY analytic function in SQL.

Can somebody help?

Below is a list of significant columns of the FATURAMENTO table.

Year - The year of the invoyce;
Month - The month of the invoyce;
Valor NF - The revenue value for that invoice;
Customer - The customer that paid the invoyce;
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mozartiano

New Member
Joined
Sep 18, 2014
Messages
18
Just to clarify a little bit, what I want to see is :

How much % customer x's payments represent of my total revenue for each year and each month. And if I selected year 2013 it should display one percentage (and total for all customers must be 100%), if I selected year 2014 it should display another percentage (and again total should be 100% for all customers and if I selected both 2013 and 2014 it should also change the percentange and display 100% for all customers.

Thanks
 

mozartiano

New Member
Joined
Sep 18, 2014
Messages
18
I keep on trying but haven't had good results.

Now I have a Measure (calculated field) called SOMA (sum in portuguese) and here is how it is set up.

SOMA:=SUMX(Faturamento;faturamento[Valor NF])/sumx(ALLEXCEPT(Faturamento;Faturamento[Ano]);Faturamento[Valor NF])

The first part (SUMX(Faturamento;faturamento[Valor NF])) calculates the Revenue for each cell using the runtime context, so if I select a different year the value changes for each customer.

The second part (/sumx(ALLEXCEPT(Faturamento;Faturamento[Ano]);Faturamento[Valor NF])) aims to divide it by the total value, without context, except the year context.

Hence, it should work since a value for a customer should be compared only to the numbers of the same year, but again, when I select only one of the years, it does not recalculate the total and I get 28% (total) for 2013 and 72%(total) for 2014 when I should get 100% for each.

Thanks in advance.
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
Nice job moving from the calculated column to a measure, that was totally correct. Calc columns are ONLY recomputed once, ever... which is when you bring in the data.

I vote we simplify a bit to make it easier to debug. WHO IS WITH ME!? :p

Revenue := SUM(faturamento[Valor NF])
Revenue - All := CALCULATE([Revenue], ALLEXCEPT(faturamento[Ano]))
SOMA := DIVIDE(Revenue, Revenue - All)

Hopefully if you put all 3 on your pivot, it helps you spot the issue. It actually reads pretty well to me.

You are almost certainly going to want a separate Calendar table, in which case your ALLEXCEPT() and other filters (like what you put on rows/columns/slicers) should come from the Calendar table, not the data/fact table. This will allow you to use all the fancy built in functions like SAMEPERIODLASTYEAR to help calculate YoY growth%, etc.
 

mozartiano

New Member
Joined
Sep 18, 2014
Messages
18

ADVERTISEMENT

Hi Scottsen,

thanks a lot for your reply.

I did what you mentioned and I can see that the denominator is not changing according to the contexts, and that is the problem, that is the reason whereby when I change the filters the percentage is shown below 100%.

Here are what the measures are like now:

Total Revenue:=CALCULATE(SUM([Valor NF]);ALLEXCEPT(Faturamento;Faturamento[Ano]))
Parcial Revenue:=SUMX(Faturamento;faturamento[Valor NF]) **** This portion of the code looks perfect, it changes according to each filter or line (customers).
SOMA:=DIVIDE([Faturamento Parcial];[Faturamento Calculado])

So,

Why isn't the context being applied over the denominator? What I understand about ALLEXCEPT() is that it removes all filters, except those that are referenced by this function, which is, in my case, the year.

As you can see, I use semicolon instead of colon because my excel does not accept the colon as separator (I am a newbie to this technology) but what is odd is how ALLEXCEPT() is written. I saw that you only passed the column to that function I was required (by powerpivot) to supply it with a tablename,columnname.

Looking forward to your comments.

Thanks

Alex
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
* My language settings (US English or whatever) have me using command, where you using semi-colon. No worries there.
* My ALLEXCEPT was wrong. You need to include the table first. ALL() takes just a column and is more commonly used, so I forgot :)
* Out of curiosity, does SUM(faturamento[Valor NF]) work just as well as SUMX(Faturamento;faturamento[Valor NF]) ? (Would be faster and preferred)
* Your understanding of ALLEXCEPT is correct (well, it removes all filters in the table specified except the columns specified, not ALL ALL filters).

I rarely do power view, so I "think" in pivot tables... so, uh... what do you have on rows/columns? Year on Columns, and Customers on Rows?

If you are comfortable with it, easier if you share your workbook (onedrive, googledrive, dropbox, whatever)
 

mozartiano

New Member
Joined
Sep 18, 2014
Messages
18

ADVERTISEMENT

Hi scottsen ,

Well, I cannot share this workbook because it contains confidential data from my customer, however I will build a fake clone and share with you guys through dropbox still today.

My pivot table has Customers on rows and this metric (% of revenue) on the column only. It is a simple table because I want the year to be controlled by the filter and not by the columns themselves.
 

mozartiano

New Member
Joined
Sep 18, 2014
Messages
18
Hi Scottsen, looks like the solution for this step was pretty simple. I will try to explain what I've learnt.

The totals are now well calculated after I did the following formulae and inserted the YEAR and MONTH columns in the pivot table (drill down enabled).

TOTAL REVENUE:=CALCULATE(SUM([Valor NF]);ALLEXCEPT(Revenue;'Year'[Year];'Month'[Month]))

Before I inserted the Year and Month in the table, it was applying this expression to the cell without any filter for the context, because the QUERY CONTEXT was only defined by CUSTOMER, which was in the row. However, when I inserted the Year and Month into the table, the QUERY CONTEXT changes to CUSTOMER x YEAR X MONTH and as ALLEXCEPT accepts filters for those columns, it recalculates the donominator whenever the Year or Month changes.

Now I face another issue because I want to see it by department instead of Customers. I will work a little bit more on this and if I need help again I will continue this thread, since the subject is still the same.

Thanks a lot for your attention and hints.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,322
Messages
5,600,951
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top