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
timoneill84
Frequent Visitor

Monthly Percentage

Hi all

 

I'm new to Power and have only done three of the tutorials so I apologise if the question has a simple answer!

 

How would I go about showing the total number of records / rows that read as certain text as a percentage of the total records that read the same text in another linked table? Pretty sure I need to do a calculated measure using Dax....

 

So for example the total "4x4 car" records in one table as a percentage of the total "4x4 car" records in another table?

 

Thanks

 

Tim

1 ACCEPTED SOLUTION

@timoneill84,

Please review this modified PBIX file.

https://1drv.ms/u/s!AhsotbnGu1NogyTKizuvFC-gvTgk

Regards,
Lydia

Community Support Team _ Lydia Zhang
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

9 REPLIES 9
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@timoneill84,

Create a measure using DAX below and check if you get expected result.

Measure 2 = CALCULATE(COUNTA(Table1[ColumnName]),FILTER(Table1,Table1[ColumnName]="4x4 car"))/CALCULATE(COUNTA(Table2[ColumnName]),FILTER(Table2,Table2[ColumnName]="4x4 car"))


If the above DAX doesn't help, please share dummy data of the two tables and post expected result based on the dummy data.

Regards,
Lydia

 

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Lydia

 

I'm getting the "Too many arguments were passed to the COUNTA function. The maximum argument count for the function is 1."

 

So one table would be like this for example:-

 

12345678      001234566     2      001234566 2       5 Door Hatchback

12345679      001234567     4      001234567 4       5 Door 4x4

12345673      001234569     7      001234569 7       3 Door 4x4

 

second table:-

 

5 Door Hatchback   10

5 Door 4x4              1

3 Door 4x4              1

 

The formula would basically need to sum the figures of rows which contain the text "4x4" (which is 2) from table two and divide by the count of the same text criteria for table one (so also 2) and then x100 making the answer here 100%.

 

I've realised there is no link / common field between the tables, does this matter?

 

Thanks 

@timoneill84,

Please check the following PBIX file.

https://1drv.ms/u/s!AhsotbnGu1NogyTKizuvFC-gvTgk

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

 

Thanks for that.

 

I've managed to replicate the check columns and they're working.

 

Having issues with the measure formula however and the overall measure %. Should there not be a sum function in there to add up the figures in table two that match the text search criteria / check?

 

Thanks

@timoneill84,

I am not clear about your requirement, could you please share expected result based on the dummy data you share?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

sorry, so here is the dummy data again (i've changed the figures that need to be summed in table two):-

 

12345678      001234566     2      001234566 2       5 Door Hatchback

12345679      001234567     4      001234567 4       5 Door 4x4

12345673      001234569     7      001234569 7       3 Door 4x4

 

second table:-

 

5 Door Hatchback   10

5 Door 4x4              6

3 Door 4x4              5

 

formula would need to do 2 (as there are 2 4x4s) divided by 11 (6+5 as the sum for text criteria containing 4x4) x100 to get a percentage figure.

 

Hope that makes sense.

@timoneill84,

Please review this modified PBIX file.

https://1drv.ms/u/s!AhsotbnGu1NogyTKizuvFC-gvTgk

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

 

I'm just trying to modify the check formula which currently reads:-

 

check = IF(ISERROR(SEARCH("4x4",Claims[Vehicle Type])),1,0)

 

How do I get it to search for multiple text criteria instead of just 4x4? so say search for 4x4 OR apples and return 0 next to those rows?

 

Thanks

@timoneill84

This thread has been closed, please open a new thread about the new question.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.