Forum Discussion

gerrymouse1's avatar
gerrymouse1
Frequent Visitor
6 years ago
Solved

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
 
 
Any help really appreciated and ap;o
 
Need Help
  • gerrymouse1's avatar
    gerrymouse1
    6 years ago

    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

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago

    gerrymouse1 

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • gerrymouse1's avatar
      gerrymouse1
      Frequent Visitor
      6 years ago

      This did not work either gave me a different result. 

  • gerrymouse1's avatar
    gerrymouse1
    Frequent Visitor
    6 years ago

    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