Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Here is my data model:
Store 1-* Sales
Item 1-* Sales
I want to show:
for each store, which items the store has not sold and the average sales for that item based on sales from other stores of that specific item.
Example:
Store A has sold $5 and $10 worth of Items 1 and 2, respectively.
Store B has sold $6 worth of Items 2 and 3 each.
Store C has sold Item 1 for $2.
I want to show a list that reads:
Store A
Item 3..............................$6
Store B
Item 1..............................$3.5
Store C
Item 2..............................$8
Item 3..............................$6
Ideally, this measure would also allow me to count the number of stores that are not selling a specific item.
Example: Using the above scenario, I would have a table that reads
Item 1..............................1
Item 2..............................1
Item 3..............................2
Solved! Go to Solution.
Hi @ajmonster ,
We can try to create a separated calculated table and use following measure in table visuals to meet your requirement:
Table = 'Item'
Measure =
IF (
CALCULATE (
COUNTROWS ( Sales ),
FILTER ( Sales, Sales[Item ID] IN DISTINCT ( 'Table'[Item ID] ) )
) = 0,
CALCULATE (
AVERAGE ( 'Sales'[Price] ),
FILTER (
ALLSELECTED ( 'Sales' ),
'Sales'[Item ID] IN DISTINCT ( 'Table'[Item ID] )
)
),
BLANK ()
)
The measure is used to filter out the items not sold in the store and return its average price
Measure 2 =
SUMX (
DISTINCT ( 'Item'[Item Name] ),
CALCULATE (
DISTINCTCOUNT ( 'Store'[Store ID] ),
ALLSELECTED ( 'Sales'[Item ID] )
)
- CALCULATE ( DISTINCTCOUNT ( 'Sales'[Store ID] ) )
)
The measure is used to count the store that didn’t sell the specific item.
Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYKEDu8HLuJAqRbVHK...
Best Regards,
Dedmon Dai
Hi @ajmonster ,
We can try to create a separated calculated table and use following measure in table visuals to meet your requirement:
Table = 'Item'
Measure =
IF (
CALCULATE (
COUNTROWS ( Sales ),
FILTER ( Sales, Sales[Item ID] IN DISTINCT ( 'Table'[Item ID] ) )
) = 0,
CALCULATE (
AVERAGE ( 'Sales'[Price] ),
FILTER (
ALLSELECTED ( 'Sales' ),
'Sales'[Item ID] IN DISTINCT ( 'Table'[Item ID] )
)
),
BLANK ()
)
The measure is used to filter out the items not sold in the store and return its average price
Measure 2 =
SUMX (
DISTINCT ( 'Item'[Item Name] ),
CALCULATE (
DISTINCTCOUNT ( 'Store'[Store ID] ),
ALLSELECTED ( 'Sales'[Item ID] )
)
- CALCULATE ( DISTINCTCOUNT ( 'Sales'[Store ID] ) )
)
The measure is used to count the store that didn’t sell the specific item.
Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYKEDu8HLuJAqRbVHK...
Best Regards,
Dedmon Dai
I've actually found a better solution:
Voids =
EXCEPT(
CROSSJOIN(
//items that have been sold in the last 4 weeks
SELECTCOLUMNS(
SUMMARIZECOLUMNS(
'Item Key'[Product Name],
FILTER(
'Item Key',
'Item Key'[4 Week Average Sales] > 0
)
),
"Product Name", 'Item Key'[Product Name]
),
//stores that have sales in the last 4 weeks
SELECTCOLUMNS(
FILTER(
'Store Key',
'Store Key'[4 Week Store Sales] > 0
),
"Store Number", 'Store Key'[Store Number]
)
),
//current 4 week sales excluding items with no sales last 4w but sales LY
SELECTCOLUMNS(
FILTER(
'Item Level',
'Item Level'[$ Sales] > 0 && 'Item Level'[Time Frame] = "Latest 4 Weeks"
),
"Product Name", RELATED('Item Key'[Product Name]),
"Store Number", 'Item Level'[Store Number]
)
)
This does exactly what I need. This is a DAX created table.
@ajmonster , Can you share better sample data and sample output.
What quality are you looking for from the sample input/output?
Item Table {Item ID}
{1, 2, 3, 4}
Store Table {Store ID}
{A, B, C, D}
Sales Table {(Store ID, Item ID, Sales Amount)}
{(A, 1, $5), (A, 2, $10), (B, 2, $6), (B, 3, $6), (C, 1, $2)}
I would like the output to be (using the table/matrix visual):
(A, 3, $6)
(B, 1, $3.5)
(C, 2, $8)
(C, 3, $6)
Hi @ajmonster ,
Have you ever try the pbix file I provided above? Did it meet your requirement?
Best Regards,
Dedmon Dai
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |