cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

Is there any solution for this?

Thanks and Regards,

Siddhant Kaushik

1 ACCEPTED SOLUTION
Employee

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.
15 REPLIES 15
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.

Helper III

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.

Employee

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

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

Helper III

Hey @v-yuezhe-msft @precedence @Anonymous

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

Frequent Visitor

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

Helper III

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?

Employee

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.
Helper III

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

Helper V

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.

Helper III

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

Thanks,

Siddhant Kaushik

Frequent Visitor

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?

Helper III

Hi @Anonymous,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors