Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
In my data I have category name, sales, and week number. For each week, I would like to calculate the total sales for the top 10 categories and then see this trend over time.
Does anyone know if this is possible?
Thanks
afk
Solved! Go to Solution.
Hi @bo_afk ,
To create measures as below.
sum = SUM(Table1[Sales])
Measure = var ran = RANKX(ALLEXCEPT(Table1,Table1[Week]),[sum],,DESC,Dense) var k =FILTER(Table1,Table1[Rank]<=2) return CALCULATE([sum],KEEPFILTERS(k))
Hi @bo_afk ,
To create the measures as below.
a = SUM(Table1[sales])
k = VAR k = ADDCOLUMNS ( Table1, "ranka", RANKX ( ALLEXCEPT ( Table1, Table1[week number] ), [a] ) ) VAR kf = FILTER ( k, [ranka] <= 10 ) RETURN CALCULATE ( SUM ( Table1[sales] ), KEEPFILTERS ( kf ) )
Measure = CALCULATE ( SUMX ( Table1, [k] ), FILTER ( ALL ( Table1 ), Table1[week number] <= MAX ( Table1[week number] ) ) )
Hi @v-frfei-msft , thanks for your response.
I tried your measures but it didn't seem to work for my data. Perhaps I didn't explain it properly.
See below a sample data set. For each week, in this example, I want to sum the sales for the top 2 categories. In Week 1 these are categories B and C with a total of 35 (20+15) and then in Week 2 these are categories A and C with a total of 31 (20+11)
Sample data | Desired Output | |||||
Week | Category | Sales | Rank | Top 2 total | ||
Week 1 | A | 10 | 3 | Week 1 | 35 | |
Week 1 | B | 15 | 2 | Week 2 | 31 | |
Week 1 | C | 20 | 1 | |||
Week 2 | A | 20 | 1 | |||
Week 2 | B | 7 | 3 | |||
Week 2 | C | 11 | 2 |
Hi @bo_afk ,
To create measures as below.
sum = SUM(Table1[Sales])
Measure = var ran = RANKX(ALLEXCEPT(Table1,Table1[Week]),[sum],,DESC,Dense) var k =FILTER(Table1,Table1[Rank]<=2) return CALCULATE([sum],KEEPFILTERS(k))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |