Not able to see sum of a measure in table

[siddhantk989]

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.

Is there any solution for this?

Thanks in advance.

Thanks and Regards,

Siddhant Kaushik

[precedence]

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.

[siddhantk989]

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.

[v-yuezhe-msft]

@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

[siddhantk989]

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

[siddhantk989]

Hey @v-yuezhe-msft @precedence @Anonymous

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

[precedence]

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

[siddhantk989]

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?

[v-yuezhe-msft · Accepted solution]

@siddhantk989，

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

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

Regards,
Lydia

[Mond]

Hi Lydia,

This post is very old and right now, I am having the similar issue.

Can you please paste the DAX formula since the link is not accessible.

Thank You

[Anonymous]

Cany ou reload the PBIX file?  The link no longer works.

[Narukkp]

Hi v-yuezhe-msft,

i am not able to find out the pbix file. Could you please let me know how to reslove this isssue. Because same issue i am facing.

[siddhantk989]

Hi @v-yuezhe-msft

Thanks a lot for the help. The solution worked perfectly fine.

Thanks,

Siddhant Kaushik

[Shrujan1612]

Hello, Siddhantk989
Would you kindly provide the file that Lydia shared with you? It's okay if you copy the measure. ASAP

[Anonymous]

Hi Siddhant,

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

[siddhantk989]

Hi @Anonymous,

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