topic Re: Merging summary tables in DAX Commands and Tips

Merging summary tables

Shwetabh147 — Tue, 16 Jul 2024 03:51:40 GMT

Hello everyone.

i have a customer dimension with store level data.

a product table with product information.
sales table which has sales at product and customer key level.

now my customer table is connected to product via sales table.

customer key from cust dimension is connected to sales product is connected to sales through gtin

Now. We have to segment the stores in 4 segments based on rank of sales.

and at the same time when a user filters a segment , then sales should change at all the attributes for product . Like we have a table in report which shows cat wise sales.
so if user clicks on 1 st segment then the data should filter accordingly.

Thanks for looking into problem

Re: Merging summary tables

bhanu_gautam — Tue, 16 Jul 2024 05:58:01 GMT

Shwetabh147 , For this first you can create a measure for total sales

Total Sales = SUM(SalesTable[SalesAmount])

Then, Create a measure for rank

Store Sales Rank =
RANKX(
ALL(CustomerDimension[CustomerKey]),
[Total Sales],
,
DESC,
DENSE
)

Create a custom column in Customer Dim table

Store Segment =
SWITCH(
TRUE(),
[Store Sales Rank] <= 25, "Segment 1",
[Store Sales Rank] <= 50, "Segment 2",
[Store Sales Rank] <= 75, "Segment 3",
"Segment 4"
)

Then you can create visuals using these measure and columns,I have taken general scenarios as you have not given sample data

Re: Merging summary tables

Shwetabh147 — Tue, 16 Jul 2024 08:18:05 GMT

I have the segments added . Problem is . There is a table where we need to showcase category level sales . Now category is in product dimension. Ask is when user selects segment, the categories should also filter for that segment.