Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
siddhantk989
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

@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

15 REPLIES 15
precedence
Frequent Visitor

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.

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.

@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.

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

Hey @v-yuezhe-msft @precedence @Anonymous

 

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

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

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?

@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.

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
Not applicable

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

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.

Hi @v-yuezhe-msft

 

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

 

Thanks,

Siddhant Kaushik

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

Anonymous
Not applicable

Hi Siddhant,

 

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

Hi @Anonymous,

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.