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

[mozartiano]

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;

 

[mozartiano]

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]

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]

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!?

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]

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]

* 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]

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.

 

[scottsen]

https://www.dropbox.com/s/rppgva88nuagr0u/PercentAll.xlsx?dl=0

Maybe try to compare to my test workbook? This is Excel 2013, I can redo in 2010 if you need.

 

[mozartiano]

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.