Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi All,
Here is a dataset .
Details :
Items : No of items per window name.
window : Name of window
type : window type
Store code : Code of store where that window & item is present
maxdate_month: Ignore
Items | month_year | window | type | Store code | maxdate_month |
2 | 202102 | abc | A | 1201 | 1 |
2 | 202102 | abc-2 | A | 1201 | 1 |
2 | 202102 | lmn | A | 1201 | 1 |
2 | 202102 | xyz | A | 1201 | 1 |
3 | 202102 | mno | A | 2603 | 1 |
6 | 202102 | abc | B | 2603 | 1 |
4 | 202102 | lmn | B | 2603 | 1 |
3 | 202102 | mno | A | 2613 | 1 |
5 | 202102 | abc | B | 2613 | 1 |
5 | 202102 | lmn | A | 2613 | 1 |
1 | 202102 | abc | A | 2622 | 1 |
1 | 202102 | lmn | A | 2622 | 1 |
1 | 202102 | xyz | B | 2622 | 1 |
2 | 202102 | mno | A | 2666 | 1 |
7 | 202102 | abc | A | 2666 | 1 |
6 | 202102 | lmn | A | 2666 | 1 |
3 | 202102 | xyz | A | 2666 | 1 |
1 | 202102 | mno | A | 2672 | 1 |
4 | 202102 | abc | B | 2672 | 1 |
4 | 202102 | abc-2 | A | 2672 | 1 |
5 | 202102 | lmn | A | 2672 | 1 |
7 | 202102 | xyz | A | 2672 | 1 |
1 | 202102 | mno | B | 2738 | 1 |
3 | 202102 | abc | B | 2738 | 1 |
4 | 202102 | lmn | A | 2738 | 1 |
1 | 202102 | xyz | A | 2738 | 1 |
4 | 202102 | mno | B | 2832 | 1 |
4 | 202102 | abc | A | 2832 | 1 |
4 | 202102 | abc-2 | A | 2832 | 1 |
6 | 202102 | lmn | A | 2832 | 1 |
1 | 202102 | abc | A | 2840 | 1 |
1 | 202102 | lmn | A | 2840 | 1 |
1 | 202102 | abc | A | 2880 | 1 |
1 | 202102 | lmn | A | 2880 | 1 |
4 | 202102 | mno | B | 2895 | 1 |
0 | 202102 | abc | A | 2895 | 1 |
2 | 202102 | mno | A | 9999 | 1 |
8 | 202102 | abc | A | 9999 | 1 |
4 | 202102 | abc-2 | A | 9999 | 1 |
4 | 202102 | lmn | A | 9999 | 1 |
0 | 202102 | xyz | A | 9999 | 1 |
Output :
Items : Sum of items (Summed up by each Store code , dynmically change as per selection of type.)
Items will be distributed as :
0
1
2
>=3
Count of (Distinct)Store Code.
Case 1: If no filter is selected then , sum no of items grouped by store code.
If type is filtered , sum of shelves will be affected .
PS : It is not count of store based on item column.
Sum of Items | NO of Stores(Distinct) | % share of column no of stores |
0 | __ | |
1 | __ | |
2 | ___ | |
>=3 |
It's not how many stores have 0 items , 1 items or so on.
Solved! Go to Solution.
Sorry, I'm finding it hard to understand what you are after. Can you elaborate and show a depiction of the expected outcome?
Proud to be a Super User!
Paul on Linkedin.
Sure. To get the total correct, change the measure to:
Store by Threshold =
VAR stores =
VALUES ( Sheet1[Store code] )
RETURN
SUMX (
'Item Threshold',
CALCULATE (
IF (
MAX ( 'Item Threshold'[Order] ) < 3,
COUNTROWS (
FILTER ( stores, [Sum Items] = SELECTEDVALUE ( 'Item Threshold'[Order] ) )
),
COUNTROWS ( FILTER ( stores, [Sum Items] > 2 ) )
)
)
)
For the % over the total, use:
% over total =
VAR _total =
CALCULATE ( [Store by Threshold], ALL ( 'Item Threshold' ) )
RETURN
DIVIDE ( [Store by Threshold], _total )
Proud to be a Super User!
Paul on Linkedin.
@Anonymous Thanks for the file
Ok, see if this is what you need.
First create a table for the thresholds using the "Enter Data" under Home in the ribbon:
Leave it unrelated in the model:
Then create the measures:
Sum Items =
SUM(Sheet1[Items])
Store by Threshold =
VAR stores =
VALUES ( Sheet1[Store code] )
RETURN
IF (
MAX ( 'Item Threshold'[Order] ) < 3,
COUNTROWS (
FILTER ( stores, [Sum Items] = SELECTEDVALUE ( 'Item Threshold'[Order] ) )
),
COUNTROWS ( FILTER ( stores, [Sum Items] > 2 ) )
)
To get
Proud to be a Super User!
Paul on Linkedin.
Sorry, I'm finding it hard to understand what you are after. Can you elaborate and show a depiction of the expected outcome?
Proud to be a Super User!
Paul on Linkedin.
Hey @PaulDBrown This is almost what is needed. Appriciate the help !!
Can you help me to calculate the % share of the threshold.
As % of stores having 0 items out of total x = a%
and so on. Currently the sum by threshold takes the >=3 value as always the total which isnt' logically correct.
Sure. To get the total correct, change the measure to:
Store by Threshold =
VAR stores =
VALUES ( Sheet1[Store code] )
RETURN
SUMX (
'Item Threshold',
CALCULATE (
IF (
MAX ( 'Item Threshold'[Order] ) < 3,
COUNTROWS (
FILTER ( stores, [Sum Items] = SELECTEDVALUE ( 'Item Threshold'[Order] ) )
),
COUNTROWS ( FILTER ( stores, [Sum Items] > 2 ) )
)
)
)
For the % over the total, use:
% over total =
VAR _total =
CALCULATE ( [Store by Threshold], ALL ( 'Item Threshold' ) )
RETURN
DIVIDE ( [Store by Threshold], _total )
Proud to be a Super User!
Paul on Linkedin.
Hey @PaulDBrown
Thanks !! Once the total is correct the % will automatically fall into place.
Appreciate your help.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |