What is the DAX for sum of percentages

Question

I have a table where I have to create percentages by looping on the same table. Here is the sample table

My first hurdle is to create the %Weightage, which for a given day, is the percentage of revenue divided by all the revenues for "that" franchise on that day. so in the example above, it is 150/(150+200+300) = 23.07%, then it is 200/650 = 30.7% and 300/650 = 46.15 (Note: I did not take reporting period 1/1/2015 because to loop, we loop the table on "that" franchise records for "that" day.)

Then I need a Franchise measure for the day, which would be Sum of (%Forecast * %Weightage). In the example here it would be Sum (91*23.07 + 97*30.7 + 92*46.15)/100 = 93.21%.

Can someone help with a DAX that can generate this Franchise% of 93.21 when i use slicer for North? Thanks

---------------Question Update on 2/26/2015------------------------

Thank you Abhijeet.

If the Revenue column was a seperate table and joined just with the Account and Date Period (1st of the month) to the main table, then I am unable to use these formulas. Revenue table had to be seperate table because they are at a month grain whereas the Franchise table is at a day grain. So Franchise Table to Revenue table is Many to One. I joined the Period and Account to create a key so I join the two tables. In the revenue table, Account and Month Period is a unique row. Here are the broken out tables, http://oi62.tinypic.com/9fsg8p.jpg

Now the Orange row on the Revenue table is valid row, where it is in the Revenue table but not being reported in the Franchise table. So when the weightage is calculated this row needs to be included too, in the Franchise South Total. Can someone guide how the DAX can be rewritten to accomodate this. Thanks I am a total beginner, and eventhough this is interesting, I find it difficult.

So i still need to calculate % Weightage and then the Franchise% Index. Thanks

Answer 1

As per your image, you have one table (let us name it to 'Franchise'). You will need to have following measures. Formulas are mentioned as

{Measure Name} = {Measure Formula}

1. Total Reported Revenue = Sum([Revenue])
2. Franchise Reported Revenue = CALCULATE(Sum([Revenue]),All(Franchise[Account]))
3. % Weightage = 100 * [Total Reported Revenue] / [Franchise Reported Revenue]
4. Total Forecast = Sum([% Forecast])
5. Account Weighted Forecast = [Total Forecast] * [% Weightage] / 100
6. Weighted Forecast =  If(HASONEVALUE(Franchise[Account]),  
                               [Account Weighted Forecast], 
                               Sumx(DISTINCT(Franchise[Account]),[Account Weighted Forecast]))

Explanation

• Measure is same as "Calculated Field" in PowerPivot. Here are links describing how to create them. Link-1 Link-2
• In the formula for measure [Account Weighted Forecast] I could have used Sum([% Forecast]) instead of measure [Total Forecast], saving me effort to create a new measure. Such reference are known as "naked column". This is not a good practice. It is better to create a new measure, which encapsulates naked column reference. Link

• Measure [Weighted Forecast] needs explanation.

  HASONEVALUE(Franchise[Account]) : Determines if current calculation is 
       for normal cell or total\subtotal cell. This will return true for 
       normal cell and false otherwise.
  
  Sumx(DISTINCT(Franchise[Account]),[Account Weighted Forecast]) : This is        
       evaluated for totals\subtotals cell. Function Sumx iterates through        
       each account and find out [Account Weighted Forecast] and them sum it.