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.
My data
Category | Sub category | Date | Value |
Cat1 | Subcat1 | 1-Jan | 1 |
Cat1 | Subcat2 | 1-Jan | 1 |
Cat2 | Subcat3 | 1-Jan | 1 |
Cat2 | Subcat4 | 1-Jan | 1 |
Cat1 | Subcat1 | 1-Feb | 2 |
Cat1 | Subcat2 | 1-Mar | 2 |
Cat2 | Subcat4 | 1-Mar | 3 |
The value ranges from 1 to 5. I am looking to create a table like below
Month | #Value 1s | #Value 2s | #Value 3s | # Value 4s | # Value 5s |
1-Jan | 4 | 0 | 0 | 0 | 0 |
1-Feb | 3 | 1 | 0 | 0 | 0 |
1-Mar | 1 | 2 (1+1) | 1 | 0 | 0 |
In Feb, 1 entry's has value changed to 2. Hence, that row from the previous month is ignored, but every other record (3 x 1s) is counted
I tried something like the below:
Cumulative Value Count =
CALCULATE(
COUNTROWS(SUMMARIZE(Table, [Cat], [Subcat])),
FILTER(ALL(Table[date]),
Table[startdate] <= MAX(Table[date])
)
)
But my output was (it is not reducing the value 1s when they have moved to 2s and 3s, ....
Month | #Value 1s | #Value 2s | #Value 3s | # Value 4s | # Value 5s |
1-Jan | 4 | 0 | 0 | 0 | 0 |
1-Feb | 4 | 1 | 0 | 0 | 0 |
1-Mar | 4 | 2 | 1 | 0 | 0 |
Any help/guidance?
The value column can have values from the range (1 to 5). These are like levels. Level 1 to 5. The Level might increase from 1 to 2 in a month or 2 or even more. Lets say a metric Category 1 and Subcategory1 is Level 1 in Jan. We will get this data. The next time we get the data is when it changes to Level 2. Lets say it happens in March. So I need to show Level 1 in Jan and Feb. And Level 2 from March.
In the output table, I am summarising how many metrics are in level 1 to level 5 in each month.
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 |
---|---|
44 | |
28 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |