March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
@timoneill84,
Please review this modified PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NogyTKizuvFC-gvTgk
Regards,
Lydia
@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
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
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
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
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
User | Count |
---|---|
122 | |
99 | |
89 | |
74 | |
66 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |