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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
o59393
Post Prodigy
Post Prodigy

KPI incorrectly gives over 100%

Hi all

 

I created a simple dax measure that sums cell values and divides all of them by 70.

 

I am getting results with over 100% which make no sense. I made sure I selected dont summarize so it wouldnt affect but still cant get it right.

 

Dax is:

 

Q/FS Total DAX = 

var _ingredients = 
(
SUM('Suppliers Compliance'[Audit Score (25%)]) + 
SUM('Suppliers Compliance'[CAP Score (5%)]) + 
SUM('Suppliers Compliance'[QF Score (20% Ing/ 30% pack)]) + 
SUM('Suppliers Compliance'[Score Threats (10%)]) + 
SUM('Suppliers Compliance'[Score Analysis (10%)])
)
/
70

var _package = 
(
SUM('Suppliers Compliance'[Audit Score (25%)]) + 
SUM('Suppliers Compliance'[CAP Score (5%)]) + 
SUM('Suppliers Compliance'[QF Score (20% Ing/ 30% pack)]) + 
SUM('Suppliers Compliance'[Score Threats (10%)]) 
)
/
70

return

IF(SELECTEDVALUE ('Suppliers Compliance'[Type])  = "Ingredients" , _ingredients  , _package)

 

Some values over 100% are

 

sum.PNG

 

I attach pbix 

 

https://1drv.ms/u/s!ApgeWwGTKtFdhjK8-nxxMvBkrAEq?e=BltJtf

 

Thanks for the help!

1 ACCEPTED SOLUTION
AiolosZhao
Memorable Member
Memorable Member

Hi @o59393 ,

 

I have checked your PBI file, that's because you have duplicated values for some IDs, so you can't only use SUM to calculate the KPI.

 

Please try to use below measure to instead of:

Q/FS Total DAX = 
var _ingredients = 
(
SUM('Suppliers Compliance'[Audit Score (25%)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[CAP Score (5%)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[QF Score (20% Ing/ 30% pack)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[Score Threats (10%)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[Score Analysis (10%)])/ COUNT('Suppliers Compliance'[Id])
)
/
70
var _package = 
(
SUM('Suppliers Compliance'[Audit Score (25%)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[CAP Score (5%)]) / COUNT('Suppliers Compliance'[Id])+ 
SUM('Suppliers Compliance'[QF Score (20% Ing/ 30% pack)]) / COUNT('Suppliers Compliance'[Id])+ 
SUM('Suppliers Compliance'[Score Threats (10%)])/ COUNT('Suppliers Compliance'[Id]) 
)
/
70
return
IF(SELECTEDVALUE ('Suppliers Compliance'[Type])  = "Ingredients" , _ingredients  , _package)

 

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@o59393 , hope above solution has resolved you issue

Hi @amitchandak  and @AiolosZhao 

 

Apologies for my delay, it worked perfectly.

 

Appreciate your time and help.

 

Have a nice weekend!

AiolosZhao
Memorable Member
Memorable Member

Hi @o59393 ,

 

I have checked your PBI file, that's because you have duplicated values for some IDs, so you can't only use SUM to calculate the KPI.

 

Please try to use below measure to instead of:

Q/FS Total DAX = 
var _ingredients = 
(
SUM('Suppliers Compliance'[Audit Score (25%)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[CAP Score (5%)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[QF Score (20% Ing/ 30% pack)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[Score Threats (10%)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[Score Analysis (10%)])/ COUNT('Suppliers Compliance'[Id])
)
/
70
var _package = 
(
SUM('Suppliers Compliance'[Audit Score (25%)])/ COUNT('Suppliers Compliance'[Id]) + 
SUM('Suppliers Compliance'[CAP Score (5%)]) / COUNT('Suppliers Compliance'[Id])+ 
SUM('Suppliers Compliance'[QF Score (20% Ing/ 30% pack)]) / COUNT('Suppliers Compliance'[Id])+ 
SUM('Suppliers Compliance'[Score Threats (10%)])/ COUNT('Suppliers Compliance'[Id]) 
)
/
70
return
IF(SELECTEDVALUE ('Suppliers Compliance'[Type])  = "Ingredients" , _ingredients  , _package)

 

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors