DAX to Calculate Weighted Percentage Based on Baseline Percentages and Revenue

[kartiklal7]

Hi All,

I have a Baseline percentages table in the following format. I need to match this data with the corresponding data in my fact table so that when someone selects one of the groupings below, they are able to see the relevant basline percentages. 

I have done that using the DAX below:

If someone selects multiple products within the same theatre and region, I need to show a weighted baseline % based on revenue and the baseline rate. 

For example, if product A has sales of 100,000 and product B has sales of 50,000 and their respective baseline percentages are 95% and 90%, then their Weighted Baseline % should be: (95.0% x 100,000 + 90.0% x 50,000) / 150,000 = 93.3%.

How do I calculate this using DAX? 

[tamerj1]

Hi @kartiklal7 

not sure how does your report look like. However, if you are slicing sales by the columns from the 'Opportunities' table the you can try

Baseline Coverage Rate =
VAR SalesCoverage =
    SUMX (
        'Baseline % Reference Table',
        VAR CoveragePetcent = 'Baseline % Reference Table'[Baseline Coverage Rate]
        VAR ClientTheatreName = 'Baseline % Reference Table'[ClientTheatreName]
        VAR ClientRegionName = 'Baseline % Reference Table'[ClientRegionName]
        VAR ProductGrouping = 'Baseline % Reference Table'[Product Grouping]
        VAR Sales =
            CALCULATE (
                SUM ( Opportunities[Sales] ),
                Opportunities[ClientTheatreName] = ClientTheatreName,
                Opportunities[ClientRegionName] = ClientRegionName,
                Opportunities[Product Grouping] = ProductGrouping
            )
        RETURN
            Sales * CoveragePetcent
    )
VAR TotalSales =
    SUM ( Opportunities[Sales] )
RETURN
    DIVIDE ( SalesCoverage, TotalSales )

[kartiklal7]

Hi @tamerj1 ,

Yes I am slicing sales from columns in the Opportunities table and also a Calendar table. 

I don't think that formula gives me what I'm looking for. See circled column. 

I have calculated sales as a percentage of the baseline (see last column) but my issue is these don't sum when there are multiple selections (I think I understand why). Is there a way to get the sum for the last column when there are multiple selections and I can use that to calculate the weighted baseline %? 

[tamerj1]

@kartiklal7 

Please provide a screenshot of your data model. Also note that the baseline coverage rate column from the baseline % reference table in not summarized in the table visual therefore it is part of the filter context. 
is Sales As Percentage of Baseling a calculated column or a measure? If a column then in which table?