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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jagriti11
New Member

Counting Rows basis of two columns

I have data like below

 

Vendor codeDuplicate basis of Bank accountDuplicate basis of name Duplicate basis of Address
Vendor A111
Vendor B110
Vendor C100
Vendor D001
Vendor E101

 

I am trying to create table visual as below but I am stuck for the vendor where more than one condition is true.

 

KPIsCountPercentage
Count of duplicate vendor basis on Bank account 480%
Count of duplicate vendor basis on name 240%
Count of duplicate vendor Address 360%

 

Above table is just illustration of real table.

 

Can any one help????

1 ACCEPTED SOLUTION
jaideepnema
Solution Sage
Solution Sage

Hi @Jagriti11 ,
Unpivot the columns excluding the vendor code and then create two measures for Percentage and Vendor Count  as shown below:

Vendor Count = CALCULATE(DISTINCTCOUNTNOBLANK(Data[Vendor code]),Data[Count]=1)
Percentage % = DIVIDE([Vendor Count],CALCULATE([Vendor Count],ALL(Data[Flag])),BLANK())

This will give you desired result:

jaideepnema_0-1613117330276.png

 

 

Please accept this as a solution if you question has been answered
Appreciate a Kudos 😀
 

View solution in original post

1 REPLY 1
jaideepnema
Solution Sage
Solution Sage

Hi @Jagriti11 ,
Unpivot the columns excluding the vendor code and then create two measures for Percentage and Vendor Count  as shown below:

Vendor Count = CALCULATE(DISTINCTCOUNTNOBLANK(Data[Vendor code]),Data[Count]=1)
Percentage % = DIVIDE([Vendor Count],CALCULATE([Vendor Count],ALL(Data[Flag])),BLANK())

This will give you desired result:

jaideepnema_0-1613117330276.png

 

 

Please accept this as a solution if you question has been answered
Appreciate a Kudos 😀
 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors