cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gerrymouse1
Frequent Visitor

Getting Wrong Totals With Measure

Folks,

 

I am totally new to Power BI and I am struggling with my first dashboard

 

I have 5 columns that have multiple Text Inputs.  I am attempting to count these inputs and display them on chart.  I created a measure that enters a 1 if the correct text is present in the column.  See below

 

UAPPEHands_GetNum_1 = IF(CONTAINS(SMATAuditData,SMATAuditData[UnsafeAct1CategoryValue], "PPE - Hands"), 1, 0)
 
This works perfectly.  However when I attempt to display this on a Chart with a year filter I only ever get 1.  I have checked through all the posts and it is clear it is to do with Filters being applied on the Rows versus the total.  I attempted to create another Measure to remove to resovle this problem but it is not functioning.
 
UAPPEHands_Filt_1 = Var _table = SUMMARIZE(SMATAuditData,SMATAuditData[UnsafeAct1CategoryValue],"__VAR", [UAPPEHands_GetNum_1])
RETURN
IF(HASONEVALUE(SMATAuditData[UnsafeAct1CategoryValue]), [UAPPEHands_GetNum_1], SUMX(_table, [__VAR]))
 
I end up with the data in the table the same without the 0'S
 
Untitled.png
 
Any help really appreciated and ap;o
 
1 ACCEPTED SOLUTION

Accepted Solutions
gerrymouse1
Frequent Visitor

Re: Getting Wrong Totals With Measure

Folks,

 

Thank you for the input.

 

I simply went back to the Query, added some Conditional Columns on the Text to get Numeric values.

 

I then created a measure in PowerBI to add the numbers nad got the correct result when entered into my reports

 

Thanks for the input and it is a pity I could not resolve with measures only

View solution in original post

5 REPLIES 5
Tad17 Solution Sage
Solution Sage

Re: Getting Wrong Totals With Measure

Hey @gerrymouse1 

 

Your field it likely set to aggregate by count or distinct count instead of sum. Check out the link below for more information on this: https://docs.microsoft.com/en-us/power-bi/service-aggregates

 

If this helps please kudo.

If this solves your problem please accept it as a solution.

Super User I
Super User I

Re: Getting Wrong Totals With Measure

@gerrymouse1 

 

Can you post a screenshot of your model and sample data of the relevant tables? (a test PBIX would be even better!)




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Community Support
Community Support

Re: Getting Wrong Totals With Measure

HI @gerrymouse1,

It will help if you share some dummy data to test, it is hard to test without any detail same data.

How to Get Your Question Answered Quickly  

In addition, you can also try to use the following measure formal if it meets for your requirement:

UAPPEHands_Filt_1 =
VAR smaCount =
    COUNTROWS (
        FILTER ( SMATAuditData, SMATAuditData[UnsafeAct1CategoryValue] = "PPE - Hands" )
    )
VAR _table =
    SUMMARIZE (
        SMATAuditData,
        SMATAuditData[UnsafeAct1CategoryValue],
        "count", IF ( smaCount > 0, 1, 0 )
    )
RETURN
    SUMX ( _table, [count] )

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
gerrymouse1
Frequent Visitor

Re: Getting Wrong Totals With Measure

This did not work either gave me a different result. 

gerrymouse1
Frequent Visitor

Re: Getting Wrong Totals With Measure

Folks,

 

Thank you for the input.

 

I simply went back to the Query, added some Conditional Columns on the Text to get Numeric values.

 

I then created a measure in PowerBI to add the numbers nad got the correct result when entered into my reports

 

Thanks for the input and it is a pity I could not resolve with measures only

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

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

Top Solution Authors
Top Kudoed Authors