Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I am creating a measure, Measure = divide(sum(amount),count(task)), this is giving me incorrect total.
This is a sample data, in which the line items of Measure = divide(sum(amount),count(task)) is correct, but the total in bottom is incorrect, could you please help.
Abhishek
Hey,
it's not totally clear, what's wrong (at least not for me)
Can you please explain what the number 11688 (Task) for Solution "A" represents
What is your expected result? And what does the Measure = Count('tablename'[Task] return for each row and also the total value.
Regards,
Tom
Hi There,
What i am trying to achieve here is :
Divide sum of amount with count of task, now what i am getting in Power BI is
Now the expected result is
Basically Power BI should return the sum of the measure i.e 17500, but it is returning 2500 which is 17500/7, how can i get the sum of the measure as total in Power BI.
Thanks
Hi,
Just want to update here, i have used
SUMX(VALUES(table1[Solution]), CALCULATE(SUM(Table2[Amount]))/CALCULATE(COUNT(task)))
and the total seems working fine for solution only, but when i drag some other dimension attribute then, it divide the values correctly, but returns an incorrect total.
Thanks,
Hi @AbhishekPandey ,
SUMX(VALUES(table1[Solution]), CALCULATE(SUM(Table2[Amount]))/CALCULATE(COUNT(task)))
and the total seems working fine for solution only, but when i drag some other dimension attribute then, it divide the values correctly, but returns an incorrect total.
What "other dimension" did you add? It looks like there are two related tables in your scenario, would you please post sample data in source tables?
Regards,
Yuliana Gu
Hi,
All the fields that i have used in the screenshot, in my previous replies are from same table only.
SUMX(VALUES(table1[Solution]), CALCULATE(SUM(Table2[Amount]))/CALCULATE(COUNT(task)))
What happening is, when i use drag solution and this measure together in form of a table, then it divides the values correctly, and the total in bottom seems correct as well.
But when i use some other dimensional attribute, for example, name then it divdes the values correctly, but the total seems to be incorrect.
The reason that, it is working fine with solution might be that i have used solution is the measure, SUMX(VALUES(table1[Solution])
that is why it is working correctly with solution.
But when i use this measure with name, then it did not worked correctly, in order to make it work for name also, i should use SUMX(VALUES(table1[Name]) instead of SUMX(VALUES(table1[Solution]).
I should not use any dimensional attribute in the values in DAX, to make it work with every dimensional attribute.
If you check my sceenshots, then you will understand what exactly i need as output.
Can anyone help on this!!
Hi @AbhishekPandey ,
Measure_2 = SUMX ( VALUES ( Table1), CALCULATE ( SUM ( Table1[Amount] ) ) / CALCULATE ( COUNT ( Table1[Task] ) ) )
Best regards,
Yuliana Gu
Hi @AbhishekPandey ,
With above measure using VALUES(TableName) instead of VALUES(TableName[Column]), whichever dimension being added into table visual, Total rows returns correct result. You could test with name and solution or any other field.
Best regards,
Yuliana Gu
Hi @v-yulgu-msft,
I have used the same calculation as shared, below are some snips. Could you please help me in this.
Here is a link to the PBIX, with some sample data.
https://www.dropbox.com/s/pfm564aq1lo3tgl/Total%20issue.pbix?dl=0
If you see in the first screenshot, it seems working fine with one dimension only, i.e Sname, the column name wrong total is sum(amount)/count(sid), the total of this calculation is incorrect,
The column correct total gives the correct output and the expression used is
Now in the second screenshot, it gives incorrect total, when another dimension Pname is dragged along with existing dimension Sname.
Thanks,
Abhishek Pandey
Hi,
Any update on this.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |