Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, I need help created a static column for Total Category Value.
I only need the total category value for 1 Quarter in my entire data set.(ex: Q2) and mark rest as 0 value.
Quarter(text) | Category(text) | Sub-Category #(Text) | Value(#) | Category Total (#) |
Q1 | Fruit | 123 | $ 15.00 | 0 |
Q1 | Fruit | 12 | $ 20.00 | 0 |
Q1 | Vegetable | 156 | $ 10.00 | 0 |
Q1 | Vegetable | 152 | $ 20.00 | 0 |
Q2 | Vegetable | 2525 | $ 30.00 | $ 80.00 |
Q2 | Vegetable | 2512 | $ 50.00 | $ 80.00 |
Q2 | Fruit | 6433 | $ 10.00 | $ 10.00 |
Q3 | Vegetable | 347 | $ 20.00 | 0 |
Q3 | Vegetable | 3636 | $ 15.00 | 0 |
Q3 | Vegetable | 98 | $ 20.00 | 0 |
Q3 | Fruit | 6596 | $ 30.00 | 0 |
Q3 | Fruit | 272 | $ 40.00 | 0 |
Q4 | Vegetable | 13 | $ 50.00 | 0 |
Q4 | Fruit | 167 | $ 30.00 | 0 |
Q4 | Vegetable | 156 | $ 10.00 | 0 |
Thanks for the suggestions!
Solved! Go to Solution.
@asalazarjr , Try a new column like
if([Quarter] ="Q2", sumx(filter(table,[Quarter] = earlier([Quarter]) && [Category] = earlier([Category])),[Value]))
Hi @asalazarjr ,
Please let us know if the replies above are helpful.
If they are, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please give us more details.
Best Regards,
Icey
@asalazarjr , Try a new column like
if([Quarter] ="Q2", sumx(filter(table,[Quarter] = earlier([Quarter]) && [Category] = earlier([Category])),[Value]))
You can use this column expression to do that
Total Category =
IF (
Table[Quarter(text)] = "Q2",
CALCULATE (
SUM ( Table[Value(#)] ),
ALLEXCEPT (
Table,
Table[Quarter(text)],
Table[Category(text)]
)
),
0
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |