Division Measure Total only based on rows with value

[Anonymous]

Hey all, 

I am fairly new to Power Bi and I'm in need of help. I have very simply put two tables that are connected through the ID column. Table A has the amount of devices and table b the amount of orders. I would like to create a measure showing the orders/per device.

Table A

ID	Date	Devices
1	10.2020	20
2	11.2020	25
3	12.2020	30

Table B 

ID	Date	Orders (GO)
1	10.2020	30
2	11.2020
3	12.2020	20

I use the following measure:

GO per Room = IF(
ISBLANK(SUM('Go per hotel'[GO])) && SUM('UDB (2)'[DeviceCount]), "N/A",SUM('Go per hotel'[GO])/SUM('UDB (2)'[DeviceCount])
)

yet encounter the problem, that my measure total takes all totals into account (so dividing 50 by 75) and not just those in which both columns have data

ID	Date	GO	Device	GO/Device
1	10.2020	30	20	1,5
2	11.2020		25	N/A
3	12.2020	20	30	0.6
Total		50	75	0.6

How would I be able to show the total, that doesn't take rows into account that have no orders(GO) for a particular month? 

Here is a screenshot of my actual data - I would like to still show the 0.49 as a total, since that has been the only row in which both columns have data in it.  

Thanks a lot for your help! 

Best, 

Hanna  

[Jihwan_Kim]

Hi, @Anonymous 

I am not sure but your desired outcome looks like using a different sample.

Please check the below picture and the sample pbix file's link down below.

Go per Device =

VAR newtable =

FILTER (

SUMMARIZE (

TableB,

TableA[ID],

TableA[Date],

"@orders", [Orders Total],

"@device", [Device Total]

),

[@orders] <> BLANK ()

&& [@device] <> BLANK ()

)

RETURN

COALESCE ( SUMX ( newtable, [@orders] ) / SUMX ( newtable, [@device] ), "N/A" )

https://www.dropbox.com/s/ht3k7aeg2il75zp/hannek.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hey @Jihwan_Kim, first of all thanks a million for helping me trying to solve this problem - I highly appreciate it!!

I do encounter the issue that Table A and Table B are only indirectly connected through the ID though, since the ID is saved in Table C - Table C has a one to many relation to Table A and B with the ID being only once  in Table C as a look up - hence the suggestion of the summarize function doesn't quite work as such in my example. 

Is it possible to still work around it somehow? 

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

Since your initial question does not mention anything about Table C (or, sorry that if I missed something), and your expected result that is shown in the initial question is, I think, not using your sample data, I assumed too many things.

Please share your sample pbix file's link here, then I can try to look into it.

Thanks.

[Anonymous]

Hey @Jihwan_Kim 

I am sorry - that's true - since a few things are confidential I tried to keep it simple - but I guess that caused more confusion than anything - but this is the reason I can't share the pbix file - yet the one you shared with me is technically exaclty as mine, just that I have 4 tables in total. 

A calendar table 

Table C with the customer ID and the customer name 

Table B with the customer ID and the amount of devices per month 

Table A with the customer ID and the amount of orders per month 

Table A and B are connected to table C by the customer ID as well as to the calendar table 

I am trying to get a matrix with the customer ID, the amount of devices and orders per month as well as with the measure of orders/devices - so technically you completely understood correctly I just forgot to mention, that table A and B are connected to table C that has the customer name and customer ID in it. 

I hope that clarified the misunderstanding? 

Thanks a lot.