Grand Total measure in row of table/matrix, with filtering

[F003Yum]

I'm trying to create a table or matrix visual that looks something like this: 

Country
Store	Date	Sales	Total Sales	Share of Sales
Nr1	2025-01-01	100	500	20%
Nr2	2025-01-01	50	500	10%
Nr3	2025-01-01	350	500	70%
Nr1	2025-02-01	200	800	25%
Nr2	2025-02-01	200	800	25%
Nr3	2025-02-01	400	800	50%

So for each day, I want the grand total of Sales, per Country to be in the "Total Sales" column, and then I just divide the sales with the "total sales" column to get my share of sales.

But I'm having an issue with the Total Sales measure.

Because I also want to be able to filter on either Store Name, or Store Owner, and an owner can have multiple stores. And so If we use the example table above, and say that Bob owns store 2 & 3, then the Total Sales for 2025-01-01 would be 50-350 = 400, and the Shares of Sales would instead be 12,5% and 87,5%

I have a DateTable with Date Column, which I use in the table above

Sales come from SalesTable

Country and Store come from StoreTable

relations look like this:

The best result I've managed to get is a total that gives a total of the whole country, but it doesn't get affected by store or store owner slicers... 

Any suggestions? Thanks!

I need the compdate table for other pages/calcualtions. 

[v-kpoloju-msft]

Hi @F003Yum,

Thank you for reaching out to the Microsoft fabric community forum. Ans thanks to @AlexTheGreat, for his inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[F003Yum]

@v-kpoloju-msft 

Thanks for the reply!

I looked at the solution, and I appreciate the attached file. 

But the issue is I need the "Total Sales (Adjusted)" to be affected by filtering on owner.

So in the example you sent:

StoreName	Sales Amount	Total Sales (Adjusted)	Share of Sales
Nr3	750	1300	0,58
Nr1	300	1300	0,23
Nr2	250	1300	0,19
Total	1300	1300	1,00

So if I select StoreOwner Bob, the table should return like this:

StoreName	Sales Amount	Total Sales (Adjusted)	Share of Sales
Nr 3	750	1000	0,75
Nr 2	250	1000	0,25
Total	1000	1000	1,0

[Ashish_Mathur]

Hi,

These measures should work

Measure = sum(Data[Sales amount])

Measure 1= calculate([Measure],all(Data[StoreName]))

Measure 2 = divide([measure],[measure 1])

Hope this helps.

[v-kpoloju-msft]

Hi @F003Yum,

Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

Outcome:

So you can change share of sales by using the below option shown in the image:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[F003Yum · Accepted solution]

@v-kpoloju-msft 

Thanks again!

That looks good, the thing is I need the measure for another calculation, I then need to multiply it with another related value to get a weighted value. 

So ValueX times Share of Sales = Weighted Value. 

I might be wrong but I don't think that'll work when I do the Show Value As thing? 

I almost got the correct way of working through this measure, this allows me to filter on Country and have the Total Sales change for all rows, and then also filter on OwnerName and have the total change for all rows, as intended. But It doesn't work when filtering on a single StoreName. I tried adding it to the ALLEXCEPT but that then gives me the normal sales value

CALCULATE(

    SUM(SalesTable[Sales]),

    ALLEXCEPT(StoreTable, StoreTable[Country], StoreTable[OwnerName]),

    USERELATIONSHIP(SalesTable[Date], DateTable[Date])

)

[v-kpoloju-msft]

Hi @F003Yum.,

Thank you for reaching out to the Microsoft fabric community forum.  I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

Outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[AlexTheGreat]

Hi,

Unfortunately your image was too pixelated so I made some assumptions of how the relationships would look like.
you should try this measure. Values() is necessary for your filters. I also made the assumption you would have a store owner in your table due to fact you descibe it in the question. 

Total Sales = 
CALCULATE(
SUM(SalesTable[Sales]),
ALL(StoreTable[Store], StoreTable[Owner]),
VALUES(DateTable[Date]),
VALUES(StoreTable[Country])
)

To get your % of shares. Just divide the total with the measure above (this works due to the fact you used values())

Share of Sales = 
DIVIDE(
SUM(SalesTable[Sales]),
[Total Sales]
)

Hope it helps! 

[F003Yum]

@AlexTheGreat 

Thanks for the reply!

Sadly this doesn't get me the answer I'm looking for in my file. 

This results in me getting the same amount as [Sales] for each store and day. resulting in always 100% share of sales.

[AlexTheGreat]

😞

If possible could you get me some dummy data to give it a second try? 
You can upload the Power BI file (no real data please) or paste some dummy data in a table.

As long as I know what we are working on.

Thanks in advance and let's figure this out!

[F003Yum]

@AlexTheGreat 

I'll see if I can prepare something, 

In the meantime, from above:

I almost got the correct way of working through this measure, this allows me to filter on Country and have the Total Sales change for all rows, and then also filter on OwnerName and have the total change for all rows, as intended. But It doesn't work when filtering on a single StoreName. I tried adding it to the ALLEXCEPT but that then gives me the normal sales value

CALCULATE(

    SUM(SalesTable[Sales]),

    ALLEXCEPT(StoreTable, StoreTable[Country], StoreTable[OwnerName]),

    USERELATIONSHIP(SalesTable[Date], DateTable[Date])

)

[AlexTheGreat]

Owh weird,

I tried your measure with @v-kpoloju-msft .pbix file and it works perfectly. Even getting the right answers with selecting a single store. (see below)

Maybe it has to do something with the relationships of your model?
Check if the relationships are similair to your model. 

Keep me posted!

[F003Yum]

@AlexTheGreat  

It's so weird, when I tried the measure above in the file @v-kpoloju-msft  provided I don't get same results, I get the normal values per row instead the total, like how it works for my file... 

But it's fine like this I guess... I can try again some other time

Thanks for helping

[v-kpoloju-msft]

Hi @F003Yum,

Thanks for the update. No worries at all if it is working well enough for now, that is perfectly fine. And if you decide to revisit it later or want to dig into why the results differ, feel free to reach out to us. Happy to help further whenever you're ready. Really appreciate you taking the time to test and follow up.

Thank you.