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,   ColumnA Country Value1 Value2 A US 23 65 A SL 75 398 A IN 5 875 ...
Need Help
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