cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Not able to see sum of a measure in table

Hi,

 

   I have a simple measure as below:

 

Measure= IF(OR([Base Amount]>5000,[Current FY Sales]>5000),(IF(([Current FY Sales] - [Monthly Target]) > 0,([Current FY Sales] - [Monthly Target]) *.15,0)),0)

 

and when I am putting this emasure along with other attributes in a table I am not getting the total sum for this particular measure.

 

Measure.PNG

 

Is there any solution for this?

 

Thanks in advance.

 

Thanks and Regards,

Siddhant Kaushik

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Not able to see sum of a measure in table

@siddhantk989


Please check if mesaure 2 in the following PBIX file returns your expected result.

https://1drv.ms/u/s!AhsotbnGu1NokysBplk7kINxJJQf

Regards,
Lydia

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

View solution in original post

13 REPLIES 13
Highlighted
Anonymous
Not applicable

Re: Not able to see sum of a measure in table

Hi Siddhant,

 

Could you right click and see if any Function has been applied to this value?

Highlighted
Helper III
Helper III

Re: Not able to see sum of a measure in table

Hi @Anonymous,

 

  thanks for replying back. There is no function applied to this value

Highlighted
Frequent Visitor

Re: Not able to see sum of a measure in table

This is a calculated measure?   

 

The total is calculated at the aggregate level.  So given all those 0s I'd it's possible that 0 is the right answer.  Can you share what the totals are for [Base Amount], [Current FY Sales], [Monthly Target]? 

 

If you want to sum up at each level, then you need to create a calculated column or do the DAX to ensure it gets calculated at the level that you want it calculated at and then summed up.

Highlighted
Helper III
Helper III

Re: Not able to see sum of a measure in table

Hi @precedence,

 

   I can't share the exact numbers, but they are not 0 at aggregate value, I am using these fields to calculate other measures and I am getting the proper value of sum in the end for those.

 

   For example I have one more measure for rebate that is using these fileds for calculation and I am getting correct result for that. Formula is as below:

 

Loyalty Rebate = IF(OR(OR(([Base Amount]>500000),AND(([Base Amount]>=125000),([Current FY Sales]> (([Base Amount]*95)/100)))), ([Current FY Sales]>125000)),IF((((3*[Current FY Sales])/100))>([Base Amount]*[% Incremented]),([Base Amount]*[% Incremented]),(3*[Current FY Sales])/100),0)

 

But in the Measure field as soon as I add an If condition to check

 

IF((([Current FY Sales] - [Monthly Target])  *.15)<=0,0,([Current FY Sales] - [Monthly Target])  *.15,0) 

 

it starts showing up the sum as 0.

Highlighted
Microsoft
Microsoft

Re: Not able to see sum of a measure in table

@siddhantk989,

Could you please share dummy data of your table so that we can test? At the total level, measure will be evaluated based on the total values of the fields you use in your DAX, it is not simply a sum value of each row measure.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper III
Helper III

Re: Not able to see sum of a measure in table

Hi @v-yuezhe-msft,

 

   Thanks a lot for replying back. I figured that out that it was calculating the new measure abased on sum to total of the 2 other measures and tht it why my total amount in the table was messing up, but is there any way to correct this situation?

 

   I am attaching a pbix file with the sample data for 1 customer but I have 100 different customers like this in the same dataset.

 

  I am focused on correcting hte field Incremneted sales which is calculated based on 2 different measures current FY sales - Monthly target.

 

   Thanks again for the help and please let me know if you find a solution to this. I am stuck on this from

 

Sampledata.pbix

 

Let me know in case you are not able to access the file

 

Thanks,

Siddhant

Highlighted
Helper III
Helper III

Re: Not able to see sum of a measure in table

Hey @v-yuezhe-msft @precedence @Anonymous

 

Did you guys got a chance to look into into the pbix file?

Highlighted
Frequent Visitor

Re: Not able to see sum of a measure in table

Have you tried using PreviousYear()?  I don't think I understand what the issue is.  

Highlighted
Helper III
Helper III

Re: Not able to see sum of a measure in table

Hi @precedence

 

  If you look at the pbix file you can see that even though the incremental rebate is having some value in it but the total is blank.

 

  Now incremental rebate is calculated as difference between Current FY Sales and Monthly Target which are again 2 measures.

 

  I understand that Incremental Rebate total is not showing any value because it is calculating SUM(Current FY Sales) - SUM(Monthly Target) and because it is less than 0 so it is showing the total value as blank based on the condition I have specified.

 

  But I just want to know is there any other way where the Total of Incremental Rebate shows the sum of positive values in its filed, i.e., I see the number 3364 in the total for Incremental rebate?

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors