Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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
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.
Hi,
The formula suggested by @Thejeswar has to be entered as a measure (not as a calculated column).
Thanks Ashish for pointing it out. That change did the trick.
You are welcome.
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 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
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
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |