Forum Discussion
Need help matrix table
Hello everyone
Need help?
I have a table with these columns:
Name, FY, YP, Target, Actual, Lead
and two calculated columns:
Quota (%), (Actual/Target)
I need a new column:
WENN Lead <=0, "S"
WENN Lead >0, "H"
But if Lead <=0 and Quota is 0, then "SS"
And I need this in a matrix
that contains the S, H, and SS
my table counts and displays
This way, I can see how many people were in each YP S, H, or SS.
And if I filter YP 1, 2, or 3, it should be added.
Thanks
Hi Hikmet_JCI
Thank you for using Microsoft Community Forum.
You can absolutely use your logic both in a calculated column for a table and in a matrix visual in Power BI. Here’s how you can approach both:
1. Calculated Column in a Table, To display this classification logic in a table visual, create a calculated column in Power BI using this DAX:
Category = IF ('Table'[Lead] <= 0 && 'Table'[Quota (%)] < 0.80,
"SS",IF ('Table'[Lead] = 0,"S",IF ('Table'[Lead] < 4,"L",IF ('Table'[Lead] = 4,"M","H"))))- Go to your table in Data view. Click New Column and paste the above code. this will create a new column (e.g., Category) with values like SS, S, L, M, or H depending on your conditions.
2. Use in a Matrix Visual, Now that you've created the column:
- Add a Matrix visual to your report.
- Use the new Category column in Rows or Columns, depending on how you want to group.
- Add your numeric values (e.g., Lead, Quota (%), etc.) in Values.
- You can also use the same logic directly in a measure if needed for calculations.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
10 Replies
I guess, you have double count
If lead <=0 for 5 items and 3 items in this 5 items, 3 have Quota = 0
You should have 5 S and 3 SS OR 2S and 3 SS
For the first case, I would suggest to create 3 measures and put them in a table
For the second case, I would create one calculated colum with the following dax:
Values = IF(TestMatrix[Lead] <=0,"S", IF(TestMatrix[Lead] > 0, "H", IF(TestMatrix[Lead] <=0 && TestMatrix[Quota] = 0, "SS")))And add this column in the Column and the values of a Matrix
Do not hesistate if you need more help
Hikmet_JCI
You can achieve this by creating a new calculated column in Power BI using DAX with your custom logic, and then using it in your Matrix visual to count the values.
Status =
SWITCH(
TRUE(),
'YourTable'[Lead] <= 0 && 'YourTable'[Quota (%)]= 0, "SS",
'YourTable'[Lead] <= 0, "S",
'YourTable'[Lead] > 0, "H"
)
If you filter YP = 1, 2, or 3 using a slicer or page filter, the matrix will update and add up the counts across the selected YPs.Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi Hikmet_JCI ,
First create a new calculated column by the bellow DAX to create the conditions:Column = IF( 'Table'[Lead] <= 0 && 'Table'[Quota (%)] = 0, "SS", IF( 'Table'[Lead] <= 0, "S", "H" ) )now create a new measure to count the YP by this DAX:
Total YP = COUNT('Table'[YP])After that, add a new Matrix, and place the YP in rows, the calculated column in column field, and the measure Total YP in values as bellow:
Based on gived data, the output will look like this:
Let me know if this satisfies your need, and feel free to ask whe something goes wrong.
If this help you, please, consider to accept this reply as solution and give a Kudo.Thank you
Hi.
Thanks.
How do I get it to display correctly when filtering in YP?
As in the image:
Filter 1-3
Leads are 6 in three periods, 3 periods / 6 = 2, so only L should appear in Column and not multiple times...Have Column new
Column =
IF(
'Table'[Lead] <= 0 && 'Table'[Quota (%)] < 0.80,
"SS",
IF(
'Table'[Lead] <= 0,
"S",
IF(
'Table'[Lead] <= 4,
"L",
IF(
'Table'[Lead] = 4,
"S",
"H"
)
)
))
I need it for a table and matrix !!just to understand,
becasue "Leads are 6 in three periods, 3 periods / 6 = 2" should only show L because L's YP = 2, right?
For example, if that calculations = 1 it was supposed to show SS? is this?
Hi Hikmet_JCI
Thank you for using Microsoft Community Forum.
You can absolutely use your logic both in a calculated column for a table and in a matrix visual in Power BI. Here’s how you can approach both:
1. Calculated Column in a Table, To display this classification logic in a table visual, create a calculated column in Power BI using this DAX:
Category = IF ('Table'[Lead] <= 0 && 'Table'[Quota (%)] < 0.80,
"SS",IF ('Table'[Lead] = 0,"S",IF ('Table'[Lead] < 4,"L",IF ('Table'[Lead] = 4,"M","H"))))- Go to your table in Data view. Click New Column and paste the above code. this will create a new column (e.g., Category) with values like SS, S, L, M, or H depending on your conditions.
2. Use in a Matrix Visual, Now that you've created the column:
- Add a Matrix visual to your report.
- Use the new Category column in Rows or Columns, depending on how you want to group.
- Add your numeric values (e.g., Lead, Quota (%), etc.) in Values.
- You can also use the same logic directly in a measure if needed for calculations.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.Hi Hikmet_JCI
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi Hikmet_JCI
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi Hikmet_JCI
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
