Values derived based on multipe criteria

[Diptarup]

Hi All,

I am new to PowerBi and working on solution wherein I have to determine the value of Country based on Product Category. Let's take an example so that it can be clear:-

Year	Country	Product Category	Product Name	Sub - Category	Value
2021	India	A	Iron	C1	0
2021	India	A	Iron	C2	1
2021	India	A	Iron	C3	1
2021	India	A	Iron	C4	0
2021	India	A	Iron	C5	1
2021	India	A	Iron	C6	0
2021	India	A	Iron	C7	0
2021	India	A	Iron	C8	0
2021	India	A	Iron	C9	0
2021	India	A	Iron	C10	0
2021	India	A	Iron	C11	1
2021	India	A	Iron	C12	1
2021	India	A	Aluminium	C1	0
2021	India	A	Aluminium	C2	1
2021	India	A	Aluminium	C3	1
2021	India	A	Aluminium	C4	0
2021	India	A	Aluminium	C5	0
2021	India	A	Aluminium	C6	0
2021	India	A	Aluminium	C7	0
2021	India	A	Aluminium	C8	0
2021	India	A	Aluminium	C9	1
2021	India	A	Aluminium	C10	1
2021	India	A	Aluminium	C11	1
2021	India	A	Aluminium	C12	0
2021	India	A	Steel	C1	1
2021	India	A	Steel	C2	0
2021	India	A	Steel	C3	1
2021	India	A	Steel	C4	0
2021	India	A	Steel	C5	1
2021	India	A	Steel	C6	0
2021	India	A	Steel	C7	0
2021	India	A	Steel	C8	1
2021	India	A	Steel	C9	1
2021	India	A	Steel	C10	0
2021	India	A	Steel	C11	1
2021	India	A	Steel	C12	1
2021	India	A	Copper	C1	0
2021	India	A	Copper	C2	0
2021	India	A	Copper	C3	0
2021	India	A	Copper	C4	0
2021	India	A	Copper	C5	0
2021	India	A	Copper	C6	0
2021	India	A	Copper	C7	1
2021	India	A	Copper	C8	0
2021	India	A	Copper	C9	0
2021	India	A	Copper	C10	0
2021	India	A	Copper	C11	0
2021	India	A	Copper	C12	1
2021	India	B	Wood	C1	1
2021	India	B	Wood	C2	0
2021	India	B	Wood	C3	1
2021	India	B	Wood	C4	1
2021	India	B	Wood	C5	1
2021	India	B	Wood	C6	0
2021	India	B	Wood	C7	0
2021	India	B	Wood	C8	1
2021	India	B	Wood	C9	0
2021	India	B	Wood	C10	0
2021	India	B	Wood	C11	0
2021	India	B	Wood	C12	0
2021	India	B	Cotton	C1	0
2021	India	B	Cotton	C2	1
2021	India	B	Cotton	C3	1
2021	India	B	Cotton	C4	1
2021	India	B	Cotton	C5	0
2021	India	B	Cotton	C6	0
2021	India	B	Cotton	C7	0
2021	India	B	Cotton	C8	1
2021	India	B	Cotton	C9	0
2021	India	B	Cotton	C10	0
2021	India	B	Cotton	C11	0
2021	India	B	Cotton	C12	0
2021	India	B	Glass	C1	0
2021	India	B	Glass	C2	1
2021	India	B	Glass	C3	1
2021	India	B	Glass	C4	1
2021	India	B	Glass	C5	0
2021	India	B	Glass	C6	0
2021	India	B	Glass	C7	0
2021	India	B	Glass	C8	1
2021	India	B	Glass	C9	0
2021	India	B	Glass	C10	0
2021	India	B	Glass	C11	0
2021	India	B	Glass	C12	0
2021	India	B	Plastic	C1	1
2021	India	B	Plastic	C2	0
2021	India	B	Plastic	C3	0
2021	India	B	Plastic	C4	1
2021	India	B	Plastic	C5	0
2021	India	B	Plastic	C6	1
2021	India	B	Plastic	C7	0
2021	India	B	Plastic	C8	1
2021	India	B	Plastic	C9	0
2021	India	B	Plastic	C10	0
2021	India	B	Plastic	C11	0
2021	India	B	Plastic	C12	0
2021	India	C	Bike	C1	1
2021	India	C	Bike	C2	1
2021	India	C	Bike	C3	1
2021	India	C	Bike	C4	0
2021	India	C	Bike	C5	0
2021	India	C	Bike	C6	0
2021	India	C	Bike	C7	0
2021	India	C	Bike	C8	0
2021	India	C	Bike	C9	0
2021	India	C	Bike	C10	0
2021	India	C	Bike	C11	1
2021	India	C	Bike	C12	1
2021	India	C	Car	C1	1
2021	India	C	Car	C2	1
2021	India	C	Car	C3	1
2021	India	C	Car	C4	0
2021	India	C	Car	C5	0
2021	India	C	Car	C6	0
2021	India	C	Car	C7	0
2021	India	C	Car	C8	0
2021	India	C	Car	C9	0
2021	India	C	Car	C10	0
2021	India	C	Car	C11	1
2021	India	C	Car	C12	1
2021	India	C	Cycle	C1	1
2021	India	C	Cycle	C2	1
2021	India	C	Cycle	C3	1
2021	India	C	Cycle	C4	0
2021	India	C	Cycle	C5	1
2021	India	C	Cycle	C6	1
2021	India	C	Cycle	C7	1
2021	India	C	Cycle	C8	1
2021	India	C	Cycle	C9	1
2021	India	C	Cycle	C10	1
2021	India	C	Cycle	C11	0
2021	India	C	Cycle	C12	1
2021	India	C	Cart	C1	1
2021	India	C	Cart	C2	1
2021	India	C	Cart	C3	1
2021	India	C	Cart	C4	0
2021	India	C	Cart	C5	1
2021	India	C	Cart	C6	1
2021	India	C	Cart	C7	1
2021	India	C	Cart	C8	1
2021	India	C	Cart	C9	1
2021	India	C	Cart	C10	1
2021	India	C	Cart	C11	0
2021	India	C	Cart	C12	1
2021	India	D		E1	4
2021	India	D		E2	3
2021	India	D		E3	2

From the above table we need to find out the value of the Countries by per Product Category. To get that we need to Add all the values given under sub category on the basis of groupings per Prodcut Category  ---

Group 1= C1+C2+C3+C4

Group 2 = C5+C6+C7+C8

Group 3= C9+C10+C11+C12

Then we need to do average of Group 1+ Group 2+ Group 3 

Taking this formula the Output for India would be like :-

India : Product Category A = Group 1 + Group 2 + Group 3

                     1.83               =     2.00      + 1.75      + 1.75

           Product Category B = Group 1 + Group 2 + Group 3

                      1.42              =   2.75       + 1.50   +    0.00 

           Product Category C = Group 1 + Group 2 + Group 3

                       2.50             =   3.00       +2.00       +    2.50

           Product Category D =        E1     + E2    + E3

                      0.67               =       1.00 +   1.00 +    0.00

Any help will be highly appreciated.

[linh091]

I made a PBI file with easy way for you. You can see step by step in the Transform:

https://drive.google.com/file/d/1KWhhYtmb1D1-zLThwT1yCLUW192p5wcY/view?usp=sharing
- Creating a group table

- Join your data with that group

- Group row with sum value

- Pivot column

- Create a average column by DAX

- 

[Diptarup]

Unfortunately, I am not able to view the file through the link. Can you please share the pbix or screenshot of the same

[linh091]

I share a folder again, you can downlaod the Powerbi file:
https://drive.google.com/drive/folders/1qZh8MtEuSvm9F-kERIX3AZ12wxWxpFoU?usp=sharing

[amitchandak]

@Diptarup ,

Create a new column

Switch ( True(),
[Sub - Category] in{ "C1","C2","C3","C4"} , "Group 1",
[Sub - Category] in{ "C5","C6","C7","C8"} , "Group 2",
[Sub - Category] in{ "C9","C10","C11","C12"} , "Group 3"
)

Create a new measure

Avg Value = averagex(Values(Table[Group]), calculate(sum(Table[Value])))

[Diptarup]

Not working, it is giving the sum value. Results I am getting are 6.33,   5.67,    10.00