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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
gerrymouse1
Frequent Visitor

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
gerrymouse1
Frequent Visitor

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

v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

This did not work either gave me a different result. 

PaulDBrown
Community Champion
Community Champion

@gerrymouse1 

 

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





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.






Tad17
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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