Forum Discussion
Measure to divide counted rows based on corresponding column value from different table
Hello everyone, hope you are well.
I am trying to figure out how to DIVIDE some counted rows based on a related group from another table. I need the count of rows from Table 2 based on the corresponding Product Type and Standard from Table 1.
(for example, since there are 3 "AA" rows counted from Table 2, divide the count(3) by the corresponding Standard from table 1(3).
I've only been able to achieve division based on a single condition thus far and I've been stuck on this for awhile. I do have these tables related as well.
Can someone please help? I have table examples listed below.
Thank you!
Table 1:
| Product Type | Standard |
AA | 3 |
| BB | 4 |
| CC | 8 |
Table 2:
| ID | Date | Product |
| X45 | 04/22/2022 | BB |
| X46 | 04/26/2022 | AA |
| X47 | 04/28/2022 | AA |
| X48 | 3/17/2022 | CC |
| X49 | 3/29/2022 | AA |
Hi Topside ,
If you need to to get the count of rows for each produc, you can do something like thisRow count for each product = CALCULATE ( COUNTROWS ( Table2 ), ALLEXCEPT ( Table2, Table2[Product] ) )For AA, this will show 3 for each ID. You can then use the measure above as the second argument in DIVIDE.
8 Replies
- VahidDM
Super User
Hi Topside
Try this measure:
Count Divide = Var _PT = MAX('Table 1'[Product Type]) Var _ST = MAX('Table 1'[Standard]) Var _T2R = CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2' ,'Table 2'[Product]=_PT)) return _T2R/_STOutput:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/Hello Vahid, I attempted this but it didn't achieve what I was looking for.
Basically I want a measure I can plug into my current table visualization that shows the counted rows of each of the categories(that are filtered out as the visualization has prexisting filters already). Then, I want to divide the COUNT by the corresponding value from Table 1 to the product type of Table 2. Something that includes something like this perhaps?:
CALCULATE(COUNTROWS(Table2),FILTER(Table2, Table2[Product] = "BB") *** need the count for each of the type of products and still divide on the matching, though- VahidDM
Is there any relationship between your tables?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hello Dan, thank you for taking time to answer. This didn't get the solution I was looking for.
Basically I want a measure I can plug into my current table visualization that shows the counted rows of each of the categories(that are filtered out as the visualization has prexisting filters already). Then, I want to divide the COUNT by the corresponding value from Table 1 to the product type of Table 2. Something that includes something like this perhaps?:CALCULATE(COUNTROWS(Table2),FILTER(Table2, Table2[Product] = "BB") *** need the count for each of the type of products and still divide on the matching, though- danextian
Hi Topside ,
If you need to to get the count of rows for each produc, you can do something like thisRow count for each product = CALCULATE ( COUNTROWS ( Table2 ), ALLEXCEPT ( Table2, Table2[Product] ) )For AA, this will show 3 for each ID. You can then use the measure above as the second argument in DIVIDE.
- Ashish_Mathur
v-yanjiang-msftCommunity Support
Hi Topside ,
Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team _ kalyj
