Monthly Percentage

[timoneill84]

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

[v-yuezhe-msft]

@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

[timoneill84]

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 

[v-yuezhe-msft]

@timoneill84,

Please check the following PBIX file.

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

Regards,
Lydia

[timoneill84]

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

[v-yuezhe-msft]

@timoneill84,

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

Regards,
Lydia

[timoneill84]

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.

[v-yuezhe-msft · Accepted solution]

@timoneill84,

Please review this modified PBIX file.

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

Regards,
Lydia

[timoneill84]

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

[v-yuezhe-msft]

@timoneill84，

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

Regards,
Lydia