cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AbhishekPandey Frequent Visitor
Frequent Visitor

Wrong Total

Hi Everyone,

 

I am creating a measure, Measure = divide(sum(amount),count(task)), this is giving me incorrect total.

 

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

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

12 REPLIES 12
Super User
Super User

Re: Wrong Total

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

  • a task id
  • the no of tasks meaning the number of rows filter implicitly by Solution "A"

What is your expected result? And what does the Measure = Count('tablename'[Task] return for each row and also the total value.

 

Regards,
Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
AbhishekPandey Frequent Visitor
Frequent Visitor

Re: Wrong Total

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 

 

Capture2.PNG

Now the expected result is 

 

Capture1.PNG

 

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

AbhishekPandey Frequent Visitor
Frequent Visitor

Re: Wrong Total

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,

v-yulgu-msft Super Contributor
Super Contributor

Re: Wrong Total

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AbhishekPandey Frequent Visitor
Frequent Visitor

Re: Wrong Total

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.

AbhishekPandey Frequent Visitor
Frequent Visitor

Re: Wrong Total

Can anyone help on this!!

 

 

v-yulgu-msft Super Contributor
Super Contributor

Re: Wrong Total

Hi @AbhishekPandey ,

 

Measure_2 =
SUMX (
    VALUES ( Table1),
    CALCULATE ( SUM ( Table1[Amount] ) ) / CALCULATE ( COUNT ( Table1[Task] ) )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AbhishekPandey Frequent Visitor
Frequent Visitor

Re: Wrong Total

Hi Yuliana,

I have already used this calculation. It seems working fine.

But i do not want to use values function here, as it will restrict the output to provide correct results for the table that i have used with values function.

What i want is, it should give correct result with any dimensional attribute, which has a relationship with this table from which amount is used.
v-yulgu-msft Super Contributor
Super Contributor

Re: Wrong Total

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

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 45 members 1,208 guests
Please welcome our newest community members: