Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
BIswajit_Das
Solution Supplier
Solution Supplier

Power BI Measures

Hello,

I have a table like

AREAVENDORPROD
11A
12B
13A
14A
25A
26B
27B
28C
39A

and i want use only measures to calculate the vendors by area

i.e

My required table matrix is like

AREAABC
1310
2121
3100

and then i want the max vendor name should there

i.e
My required table :

AREAABC
1A  
2 B 
3A  

I do not want to use calculated columns
Is there any way to do so with measure only
Thanks & Regards...

1 ACCEPTED SOLUTION
v-jialongy-msft
Community Support
Community Support

Hello @BIswajit_Das 

For your question, I have created the following table

vjialongymsft_0-1702452717143.png

You can use the settings below to get the matrix style you want

vjialongymsft_1-1702452751094.png

 

 

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())

vjialongymsft_2-1702452789623.png

 

 

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jialongy-msft
Community Support
Community Support

Hello @BIswajit_Das 

For your question, I have created the following table

vjialongymsft_0-1702452717143.png

You can use the settings below to get the matrix style you want

vjialongymsft_1-1702452751094.png

 

 

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())

vjialongymsft_2-1702452789623.png

 

 

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
Community Champion
Community Champion

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.