Skip to main content
cancel
Showing results for 
Search instead 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

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

 

error.pngquery.PNG

1 ACCEPTED SOLUTION
Thejeswar
Resident Rockstar
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.

 

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

View solution in original post

11 REPLIES 11
Thejeswar
Resident Rockstar
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.

 

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

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

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
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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. 

Welcome..

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 NameMobileUsagenum_Learners
ABC7281754
DEF124546
GHI985625
DEF215212
GHI689322
DEF546842
GHI8661622

 

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 NameMobileUsage
ABC728
DEF124
GHI985
DEF215
GHI689
DEF546
GHI866

 

Table: ActiveAccount-14May2018

Org Namenum_Learners
ABC1754
DEF546
GHI625
DEF212
GHI322
DEF842
GHI1622

 

DAX Code:

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

Report Display:

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

 

Regards,

Thejeswar

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

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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