cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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.

1 ACCEPTED SOLUTION
Resident Rockstar

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.

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

Regards,

Thejeswar

11 REPLIES 11
Resident Rockstar

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.

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

Regards,

Thejeswar

New Member

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.

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
New Member

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resident Rockstar

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

New Member

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.

Resident Rockstar

Welcome..

Resident Rockstar

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

New Member

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

Resident Rockstar

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors