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.
I am going to optimize a excel report by using Power BI. For the Amount columns, i do not have the idea how to create measure/column because depending on different activity, brand or channel, it will have different calculation for the amount. How should i deal with this kind of problem?
My expected goal is to create a table with this format:
Type | Activity | Channel | Brand | Amount |
Type A | Brand A regular discount | Channel A | Brand A | 12345 |
Type B | Brand A additional discount | Channel A | Brand A | 23456 |
Type C | Brand A display | Channel A | Brand A | 34567 |
Solved! Go to Solution.
Hello @eunji888888,
You can use nested IF or SWITCH function or filter using CALCULATE. I can help you with SWITCH function as an example:
SWITCH(TRUE(),
'Table'[Activity] = "Brand A regular discount" && 'Table'[Channel] = "Channel A" && 'Table'[Brand] = "Brand",write your first measure here,
'Table'[Activity] = "Brand A additional discount" && 'Table'[Channel] = "Channel A" && 'Table'[Brand] = "Brand",write your second measure here,
.
.
.
.
Blank())
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
Hi,@eunji888888.I am glad to help you.
Hello,@Kishore_KVN, thanks for your concern about this issue.Your answer is excellent!And I would like to share some additional solutions below.
Have your needs been solved?
Here are some practical examples to add to the suggestions I made to @Kishore_KVN.
Here are the similar cases: I hope you find them helpful!
Discussing and executing corresponding MEASURES by categorizing different brands of products is a very good way to solve your needs.
In the table or matrix can achieve this effect, I hope the following examples can give you a good inspiration
M_result =
SWITCH(TRUE(),
SELECTEDVALUE(Header[category])="Material",
SWITCH(TRUE(),
SELECTEDVALUE(Header[name])="Wood",[M_1],
SELECTEDVALUE(Header[name])="Particle Board",[M_1],
SELECTEDVALUE(Header[name])="Iron",[M_1]
),
SELECTEDVALUE(Header[category])="Others",
SWITCH(TRUE(),
SELECTEDVALUE(Header[name])="%Good",[M_%Good],
SELECTEDVALUE(Header[name])="%Not Good",[M_%Not_Good]
)
)
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,@eunji888888.I am glad to help you.
Hello,@Kishore_KVN, thanks for your concern about this issue.Your answer is excellent!And I would like to share some additional solutions below.
Have your needs been solved?
Here are some practical examples to add to the suggestions I made to @Kishore_KVN.
Here are the similar cases: I hope you find them helpful!
Discussing and executing corresponding MEASURES by categorizing different brands of products is a very good way to solve your needs.
In the table or matrix can achieve this effect, I hope the following examples can give you a good inspiration
M_result =
SWITCH(TRUE(),
SELECTEDVALUE(Header[category])="Material",
SWITCH(TRUE(),
SELECTEDVALUE(Header[name])="Wood",[M_1],
SELECTEDVALUE(Header[name])="Particle Board",[M_1],
SELECTEDVALUE(Header[name])="Iron",[M_1]
),
SELECTEDVALUE(Header[category])="Others",
SWITCH(TRUE(),
SELECTEDVALUE(Header[name])="%Good",[M_%Good],
SELECTEDVALUE(Header[name])="%Not Good",[M_%Not_Good]
)
)
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @eunji888888,
You can use nested IF or SWITCH function or filter using CALCULATE. I can help you with SWITCH function as an example:
SWITCH(TRUE(),
'Table'[Activity] = "Brand A regular discount" && 'Table'[Channel] = "Channel A" && 'Table'[Brand] = "Brand",write your first measure here,
'Table'[Activity] = "Brand A additional discount" && 'Table'[Channel] = "Channel A" && 'Table'[Brand] = "Brand",write your second measure here,
.
.
.
.
Blank())
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |