Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I'm not a DAX specialist and struggeling with the following task:
I need to calculate a ratio the records (1200) of the column "field1" in "table1" against the records (845) column "field2" in "table2" - guess I ihave to use the "Measure" functionality. In a first step, the result should in percent, so I have to turn the values and calculate it x10 > like as 840/1200=0.7*10
In a second step, I'll have another "field3" in both tables which is the same. This is a category. I need to show the above result according to this category and must have this as a pie chart. Result would then be, that I can click into the chart of "field3" and the measure above is shown for the selected category.
Would very appreciate if one of the cracks can help me!
Many thanks!
Patrick
Solved! Go to Solution.
I'm guessing that this is what you're looking for ...
Ratio =DIVIDE( COUNTROWS(Table 1), COUNTROWS(Table 2))
That will return a percentage - you may need to set the formatting on the colum to %
You be able to filter both tables by field 3 only if the tables are related to each other in some way - you may need to create a third table that you can use to filter both tables.
To set the formatting as % you simply select the measure in the model (within desktop) and select the appropriate formatting options. If I can attach the snip it will be of the ribbon showing where the options are.
Proud to be a Super User!
Here's the right way to add in the filters ...
COUNTROWS(FILTER(Table1[Product Line], Table1[Product Line] = "PL1" || Table1[Poduct Line] = "PL2" || Table1[Product Line] = "PL3") ) , etc ...
I'm guessing that this is what you're looking for ...
Ratio =DIVIDE( COUNTROWS(Table 1), COUNTROWS(Table 2))
That will return a percentage - you may need to set the formatting on the colum to %
You be able to filter both tables by field 3 only if the tables are related to each other in some way - you may need to create a third table that you can use to filter both tables.
Hello Austin
It works! Thanks!
But now I'm struggeling with the "FILTER" function because I'm having "Text" in the columns with 5 different values, but I need only 3 of them... So, after reading the DAX reference I'll changed "COUNTROWS" into "COUNTA" and tried the follwing - but it seems I need to put another argument in:
BC Coverage PL1 = DIVIDE( COUNTA (FILTER ('CRM Compressor'[Product Line] = "PL1","PL2","PL3"); COUNTA (FILTER ('Z_Equianal'[PL] = "PL1","PL2","PL3"))))
Here's the right way to add in the filters ...
COUNTROWS(FILTER(Table1[Product Line], Table1[Product Line] = "PL1" || Table1[Poduct Line] = "PL2" || Table1[Product Line] = "PL3") ) , etc ...
Hello Austin
Thanks, I'll try that and will come back...
Question: "you may need to set the formatting on the colum to %"
How can I do that? Was looking for this long time but couldn't find that in any menu... Will this be done with DAX too?
Best Regards
Patrick
To set the formatting as % you simply select the measure in the model (within desktop) and select the appropriate formatting options. If I can attach the snip it will be of the ribbon showing where the options are.
Proud to be a Super User!
Many thanks - I was blind.... always searching in the Query Editor...!
Best regards
Patrick
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |