Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Wrong Total

Hi All,

 

I get wrong subtotal even trying with sumx-summarize & Hasonevalue as well help me in this,

 

ColumnACountryValue1Value2
AUS2365
ASL75398
AIN5875
BUK4629
BUS9856
BSL234597
CUS45645
DAU073
EUS27329

 

the columnA has to be filtered from other table having common colum and if country is "US" I have to take value1 for final calculation and if the country is other than "US"  I have to take value2 for calculation

 

I write a measure like

 

Measure =
Var F_Val = IF(TB[Country] = "US",SUM([val1]),SUM([val2]))
Var Summ_ = summarize(TB,ColumnA,"Val",[F_Val])
Var Result = sumx(summ_,[Val])
Return

Result

 

Measure 2 = IF(TB[Country] = "US",SUM([val1]),SUM([val2]))

 

Measure 3 =
Var Summ_ = summarize(TB,ColumnA,"Val",[Measure 2])
Var Result = IF(HASONEVALUE(ColumnA),[Measure 2],sumx(summ_,Val))
Return 

Result

 

Please find out my mistake and help me out this

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  ,try below measures

     

    Measure 1:

    Measure = VAR F_Val = IF(TB[Country] = "US", SUM([val1]), SUM([val2])) VAR Summ_ = SUMMARIZE(TB, TB[ColumnA], "Val", [F_Val]) VAR Result = SUMX(Summ_, [Val]) RETURN Result

    In this measure, the main issue is with the SUMMARIZE function. The SUMMARIZE function is used to group and summarize data, but in your case, it doesn't seem necessary. Instead, you can directly calculate the F_Val and return it as the measure value.

     

    Here's an updated version of Measure 1:

    Measure = VAR F_Val = IF(TB[Country] = "US", SUM(TB[val1]), SUM(TB[val2])) RETURN F_Val

     

     

    Measure 2:

    Measure 2 = IF(TB[Country] = "US", SUM([val1]), SUM([val2]))

    Measure 2 seems fine, as it calculates the appropriate value based on the condition. However, make sure you have the correct column names (val1 and val2) in your actual table.

    Measure 3:

    Measure 3 = VAR Summ_ = SUMMARIZE(TB, TB[ColumnA], "Val", [F_Val]) VAR Result = IF(HASONEVALUE(TB[ColumnA]), [Measure 2], SUMX(Summ_, [Val])) RETURN Result

     

     

    In Measure 3, you are using F_Val in the SUMMARIZE function, but F_Val is not defined in this measure. Also, the condition HASONEVALUE(TB[ColumnA]) won't work as expected because the ColumnA is part of the SUMMARIZE function, and it will always have multiple values.

    Here's an updated version of Measure 3:

     

    sql

     

     

     

    Measure 3 = VAR Result = IF(TB[Country] = "US", SUM(TB[val1]), SUM(TB[val2])) RETURN Result

     

    Anonymous

  • SamInogic's avatar
    SamInogic
    Super User
    3 years ago

    Hi Anonymous , 

     

    As per our understanding, you are looking for a way to get the total of Value 1 and Value 2 based on the Country cell value,

     

    In Power BI, I think we cannot access the columns of the table and we need to use selectedvalue in the Measure 1 and Measure 2 to get specific column’s current value within each row,

     

    Can you try to replace your measures with below DAX expression to check if works? 

     

    Measure_1 = var F_Val = if(SELECTEDVALUE(SampleTable[Country])="US",SUM(SampleTable[Value1]),SUM(SampleTable[Value2]))

    var Summ = SUMMARIZE(SampleTable,SampleTable[ColumnA],"Val",F_Val)

    var Result = SUMX(Summ,[Val])

    return Result

    Measure_2 = IF(SELECTEDVALUE(SampleTable[Country]) = "US",SUM(SampleTable[Value1]),SUM(SampleTable[Value2]))

    Measure_3 =

    var Summ = SUMMARIZE(SampleTable,SampleTable[ColumnA],"Val1",SUM(SampleTable[Value2]))

    var result = IF(HASONEVALUE(SampleTable[ColumnA]),[Measure_2],SUMX(Summ,[Val1]))

    return result


    Please Refer the below Screenshot, 


    Still, if the above screenshot doesn't satisfy your requirement, then you can provide us the expected output by hiding the sensitive data.

     

    Thanks!

    Inogic Professional Services Division

    Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

    Drop an email at [email protected]

    Services:  http://www.inogic.com/services/

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      HI SamInogic  the measure gives correct values in row but the overall total I get is wrong