How to Calculate % of Online Sales when Total Sales are provided in different ways

[RandomRaisin]

Hi, I am new to Power BI and am trying to create a measure that calculates the percentage of sales that were done online, but each store provides their sales differently.  Some stores provide in store and online sales by transaction while other stores only provide online total and combined total sales (see below for example)

$ Sales	Channel	Store
50	In Store	A
25	Online	A
70	In Store	A
15	Online	B
80	Total	B

How do I create a measure that sums up all online sales, then divides by total sales?

My trouble is calculating the total sales because I need the measure to combine all Store A sales, but only take Total from Store B and not add Online to Store B's total sales.

In this example, the measure would be:

Online / Total --> (25+15) / (50+25+70+80) = 40 / 225 = 17.8%

For context, there are a total of 16 stores, 7 of which provide online and total sales, while 9 stores provide In Store and Online Sales.

The farthest I've been able to get is this:

Online % Share of Total Sales =

calculate(sum('ALL RETAILERS'[$ Sales]),'ALL RETAILERS'[Channel]="Online") /

(

CALCULATE(sum('ALL RETAILERS'[$ Sales]),'ALL RETAILERS'[Channel]="In Store") +

CALCULATE(sum('ALL RETAILERS'[$ Sales]),'ALL RETAILERS'[Channel]="Total") +

if(CALCULATE(SUM('ALL RETAILERS'[$ Sales]),'ALL RETAILERS'[Channel]="Total")=0,CALCULATE(SUM('ALL RETAILERS'[$ Sales]),'ALL RETAILERS'[Channel]="Online"),0)

)

[parry2k · Accepted solution]

@RandomRaisin try these two measures, you can always divide these into seperate measures:

Sum Sales = SUM ( [$ Sales] )


Total Sales = 
VAR __StoreWithTotalSales = 
CALCULATETABLE ( 
    VALUES ( 'Table'[Store] ),  
    'Table'[Channel] = "Total" 
) 
VAR __StoreThatHasOnlineSalesWithTotal = 
CALCULATE ( 
    [Sum Sales], 
    __StoreWithTotalSales,  
    'Table'[Channel] = "Online" 
) 
VAR __OnlineSales = 
CALCULATE (
    [Sum Sales],
    'Table'[Channel] = "Online" 
)
VAR __TotalSales = CALCULATE ( [Sum Sales], ALL ( 'Table' ) )
RETuRN DIVIDE ( __OnlineSales, __TotalSales - __StoreThatHasOnlineSalesWithTotal )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[RandomRaisin]

Thank you @parry2k! I changed the calculations a little bit, but using Variables with CALCULATETABLE worked perfectly for me!  Thank you so much!