Need help to write a measure to DIVIDE values from columns from separate tables

[Mukul_Lele]

I want to divide value from column-1 with value from column-2. I use a measure and a DIVIDE function. See the snapshot below. Note that both comumns are in different tables. Also note that total rows (entries) in table-2 is lesser than table-1. 

I get the following error (see snapashot) everytime I try this out. 

Any help to solve this issue?

I want to divide two values and show it as a percentage on a Card visualization. 

[Thejeswar · Accepted solution]

Hi @Mukul_Lele,

The Error message that you get is because in the DAX Calculation that you are using the Numerator and the Denominators are supplying multiple values where they are expected to give only one value.

i.e. You should group your numerator and denominator values by some aggregate functions such as SUM, AVG, MIN, MAX etc.

Also, since this is a measure, your grouped measure will automatically split for the level of aggregation they are used with. So no worries about the value.

Hence in your case, your DAX Measure should be as follows

column = DIVIDE(SUM('MobileData'[MobileUsage]),SUM('ActiveAccount-14May2018'[num_learners]))*100

Hope this solves your issue!!!

Regards,

Thejeswar

[Mukul_Lele]

Tried it, query worked but did not give me expected value. 

For a specific customer entry I select, I expect this rule to divide mobile usage / num_Learners values for this customer row only. Does not seem to work. Result value is not the correct division. 

Aplologies in case I am not using correct terms to explain my problem. I am just few days into PowerBI and still gearing up. Attached snapshot should help. 

[Ashish_Mathur]

Hi,

The formula suggested by @Thejeswar has to be entered as a measure (not as a calculated column).

[Mukul_Lele]

Thanks Ashish for pointing it out. That change did the trick. 

[Ashish_Mathur]

You are welcome.

[Thejeswar]

Hi,

Rightly said  by @Ashish_Mathur.

@Mukul_Lele, hope you are using the DAX as a measure. If you are using this as a column, then make it a measure.

I have mentioned to create it as a measure in my First Post on this topic

Regards,

Thejeswar

[Mukul_Lele]

Hello,

Thanks. It worked well. I was using it as a 'column'. Changed it to 'measure' and it worked fine. 

Tried to read and understand the difference between both of them. Unfortunately still not very clear. I could understand that columns are processed during the data loading time and measures are computed in the context. But functional difference between the two is not clear to me. Hence wondering why 'column' kept giving me 3.74 result throughtout all the rows in the column. 

It would be great if you can point me to any readings which will help me to differentiate better. 

Overall ... thank you for helping me solve this problem. 

[Thejeswar]

Welcome..

[Thejeswar]

Hi @Mukul_Lele,

I tried the scenario that you posted. I still find the calculation to give me the right information.

Are you sure of your report observation??

I have added the images of my Data consideration, DA Formula and Report Display for your cross verification

Scenario 1: All my data in a single table

My Data:

Org Name	MobileUsage	num_Learners
ABC	728	1754
DEF	124	546
GHI	985	625
DEF	215	212
GHI	689	322
DEF	546	842
GHI	866	1622

DAX Code:

Measure = DIVIDE(SUM(Table1[MobileUsage]),SUM(Table1[num_Learners])) *100

Scenario 2: When Data is in 2 different tables

Data:

Table: MobileData

Org Name	MobileUsage
ABC	728
DEF	124
GHI	985
DEF	215
GHI	689
DEF	546
GHI	866

Table: ActiveAccount-14May2018

Org Name	num_Learners
ABC	1754
DEF	546
GHI	625
DEF	212
GHI	322
DEF	842
GHI	1622

DAX Code:

Measure = DIVIDE(SUM(MobileData[MobileUsage]),SUM('ActiveAccount-14May2018'[num_Learners])) *100

Report Display:

My Report Display remains the same in both the instances. Please check your report once again....

Regards,

Thejeswar

[Mukul_Lele]

Hello,

Thanks for the detailed explanation. I checked and rechecked but it still is not working for me. 

It shows the value as 3.74 for all the rows in my 'Measure' column. I will continue to look for any errors done from my side. 

Just one question - 

In my data, MobileUsage entries are lesser than num_learners entries. Say there are 10 rows of data for MobileUsage, rest all rows are empty. num_learners data is filled up for all rows of the Organization. Does this cause any problem in using the sum and the divide? Just chhecking if this might be causing any issue. 

Mukul

[Thejeswar]

Hi @Mukul_Lele,

The Difference in the no. of rows shouldn't cause any issue as long as your joins are proper, per my understanding.

But since you say that 3.74 is getting repeated for all the rows, it could be a case of cross join or someother issue with the join between the two tables (MobileData and ActiveAccount-14May2018.

If not, it could be an aggregation issue which is resulting in same numerator and denominator values for all organization in the measure. I suppose this is not your case.

Apart from this, I don't see any potential case for this issue

In both the above mentioned cases, we might have to check the relationships between the tables in your data model

So verify if your data model joins are appropriate

Rergards,

Thejeswar