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

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
Microsoft Employee

@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.
9 REPLIES 9
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.
Frequent Visitor

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

Microsoft Employee

@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.
Frequent Visitor

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

Microsoft Employee

@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.
Frequent Visitor

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.

Microsoft Employee

@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.
Frequent Visitor

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

Microsoft Employee

@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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors