Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello friends ,
I need to calculate the sum of units and sum of revenue in the below table based on the following condition:
if Code = a and code = any other then calculate the sum of units and revenue under Category1
If code = b and (code = c or code =d) and (code not a ) and code = any other calculate the sum under Category2
If code = b and ( code not c ) and (code not d ) and (code not a ) and code = any other calculate the sum under Category3
Please note that the sum of units should calculate only the average sum ( meaning for ID =1 , the sum of units of this particualar ID is 2
the sum of revenue is the sum of amounts in all lines ,for instanse :sume of revenue for ID=1 is 2890
ID | Count of Units | Code | Revenue |
1 | 2 | a | 20 |
1 | 2 | b | 500 |
1 | 2 | c | 100 |
1 | 2 | d | 20 |
1 | 2 | e | 2000 |
1 | 2 | f | 200 |
1 | 2 | g | 50 |
2 | 3 | b | 60 |
2 | 3 | b | 700 |
2 | 3 | c | 400 |
2 | 3 | d | 300 |
2 | 3 | b | 100 |
2 | 3 | g | 11 |
2 | 3 | f | 212 |
2 | 3 | g | 236 |
2 | 3 | f | 254 |
3 | 5 | b | 20 |
3 | 5 | g | 30 |
3 | 5 | i | 55 |
3 | 5 | h | 60 |
3 | 5 | m | 80 |
Hello @Dubai80,
Can you please try:
1. Create a New Table
Categories =
UNION(
ROW("Code", "a", "Category", "Category1"),
ROW("Code", "b", "Category", "Category2"),
ROW("Code", "c", "Category", "Category2"),
ROW("Code", "d", "Category", "Category2"),
ROW("Code", "e", "Category", "Category1"),
ROW("Code", "f", "Category", "Category1"),
ROW("Code", "g", "Category", "Category1"),
ROW("Code", "h", "Category", "Category3"),
ROW("Code", "i", "Category", "Category3"),
ROW("Code", "m", "Category", "Category3")
)
2. Create Measures
Total Units = SUM('YourTableName'[Count of Units])
Total Revenue = SUM('YourTableName'[Revenue])
Category1 Units =
SUMX(
FILTER(Categories, Categories[Category] = "Category1"),
[Total Units]
)
Category1 Revenue =
SUMX(
FILTER(Categories, Categories[Category] = "Category1"),
[Total Revenue]
)
Category2 Units =
SUMX(
FILTER(Categories, Categories[Category] = "Category2"),
[Total Units]
)
Category2 Revenue =
SUMX(
FILTER(Categories, Categories[Category] = "Category2"),
[Total Revenue]
)
Category3 Units =
SUMX(
FILTER(Categories, Categories[Category] = "Category3"),
[Total Units]
)
Category3 Revenue =
SUMX(
FILTER(Categories, Categories[Category] = "Category3"),
[Total Revenue]
)
Avg Units Per ID =
AVERAGEX(
SUMMARIZE('YourTableName', 'YourTableName'[ID], "Total Units", [Total Units]),
[Total Units]
)
Should you require further assistance, please do not hesitate to reach out to me.
Thanks Sahir for the reply .
However , this is not the required .
I have explained the logic in the post .
Take ID=1 as an example .
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |