Measure for calculating rows of different tables

[Willborn]

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

[austinsense · Accepted 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.

[kcantor · Accepted solution]

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.

[austinsense · Accepted 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.

[Willborn]

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"))))

[austinsense · Accepted solution]

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 ...

[Willborn]

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 

[kcantor · Accepted solution]

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.

[Willborn]

Many thanks - I was blind.... always searching in the Query Editor...!

Best regards

Patrick