Forum Discussion
Power BI Measures
Hello,
I have a table like
| AREA | VENDOR | PROD |
| 1 | 1 | A |
| 1 | 2 | B |
| 1 | 3 | A |
| 1 | 4 | A |
| 2 | 5 | A |
| 2 | 6 | B |
| 2 | 7 | B |
| 2 | 8 | C |
| 3 | 9 | A |
and i want use only measures to calculate the vendors by area
i.e
My required table matrix is like
| AREA | A | B | C |
| 1 | 3 | 1 | 0 |
| 2 | 1 | 2 | 1 |
| 3 | 1 | 0 | 0 |
and then i want the max vendor name should there
i.e
My required table :
| AREA | A | B | C |
| 1 | A | ||
| 2 | B | ||
| 3 | A |
I do not want to use calculated columns
Is there any way to do so with measure only
Thanks & Regards...
Hello BIswajit_Das
For your question, I have created the following table
You can use the settings below to get the matrix style you want
Use the following measure to get the max vendor
Measure = var _table=SUMMARIZE(ALL('Table'),'Table'[Area],'Table'[prod],"count",COUNT('Table'[vendor])) var _max= MAXX(FILTER(_table,[Area] in VALUES('Table'[Area])),[count]) return IF(COUNT('Table'[vendor])=_max,MAX('Table'[prod]),BLANK())Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Here's a step-by-step guide:
Count of Vendors by Area:
Create measures to count the vendors for each area. Assuming your table is named "YourTable," create the following measures:
DAXVendors_A = CALCULATE(COUNTAX(FILTER(YourTable, YourTable[PROD] = "A"), YourTable[VENDOR])) Vendors_B = CALCULATE(COUNTAX(FILTER(YourTable, YourTable[PROD] = "B"), YourTable[VENDOR])) Vendors_C = CALCULATE(COUNTAX(FILTER(YourTable, YourTable[PROD] = "C"), YourTable[VENDOR]))These measures count the number of vendors for each product category (A, B, C) within each area.
Create Matrix Table:
Now, you can create a matrix table with the AREA on rows and the product categories (A, B, C) on columns. Place the respective measures in the values section of the matrix.
Max Vendor Name:
To get the maximum vendor name for each area, create the following measure:
DAXMaxVendor_Area = SWITCH(TRUE(), [Vendors_A] >= [Vendors_B] && [Vendors_A] >= [Vendors_C], "A", [Vendors_B] >= [Vendors_A] && [Vendors_B] >= [Vendors_C], "B", [Vendors_C] >= [Vendors_A] && [Vendors_C] >= [Vendors_B], "C", BLANK() )This measure uses the SWITCH function to determine which product category has the maximum count for each area.
Now, you can add the MaxVendor_Area measure to your matrix table. It will display the maximum vendor name for each area.
Your final matrix table should look like the one you described, with counts for each product category and the corresponding maximum vendor name for each area. Remember to replace "YourTable" with the actual name of your table in Power BI.
In case there is still a problem, please feel free and explain your issue in detail and share sample file, It will be my pleasure to assist you in any way I can.
Hello BIswajit_Das
For your question, I have created the following table
You can use the settings below to get the matrix style you want
Use the following measure to get the max vendor
Measure = var _table=SUMMARIZE(ALL('Table'),'Table'[Area],'Table'[prod],"count",COUNT('Table'[vendor])) var _max= MAXX(FILTER(_table,[Area] in VALUES('Table'[Area])),[count]) return IF(COUNT('Table'[vendor])=_max,MAX('Table'[prod]),BLANK())Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
