Power BI Measures

[BIswajit_Das]

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...

[v-jialongy-msft · Accepted solution]

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.}

[123abc]

Here's a step-by-step guide:

1. Count of Vendors by Area:

   Create measures to count the vendors for each area. Assuming your table is named "YourTable," create the following measures:

   DAX

   Vendors_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.

2. 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.

3. Max Vendor Name:

   To get the maximum vendor name for each area, create the following measure:

   DAX

   MaxVendor_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.

1. 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.