Forum Discussion

miriammmattar's avatar
miriammmattar
Frequent Visitor
4 years ago
Solved

Table not summing correctly with a variable dax equation

Hi

I have data with revenue values per "some" Market Segments (that are of interest) and per location. I also have in the same table the total revenue for all locations. I created a dax equation to calculate the difference between the total and the values of the selected market segments as "Other" but when I put this equation in a table it doesn't sum up correctly in the Total. it should be for example (below) 21 but it's not adding the "Other" line and showing the sum of the rest

How can I fix this please?

thanks

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi miriammmattar ,

     

    If you want to calcualte the sum of other markets for each market, please simply try:

    Other = CALCULATE(SUM('Table'[Values]),FILTER(ALL('Table'),[Market Segment]<>MAX('Table'[Market Segment])))

     

    Or it's not your expected output, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • vojtechsima's avatar
    vojtechsima
    Icon for Super User rankSuper User
    4 years ago

    Hi, miriammmattar ,
    I am not sure I am getting you correctly, but maybe this?

    SomethingOrOther = 
var AllValues = VALUES(MarketTable[Market Segement])
var Market = SELECTEDVALUE(MarketTable[Market Segement])
var AllExceptOTher = CALCULATE(SUM(MarketTable[Values]), MarketTable[Market Segement] <> "Other")
var OtherValues = CALCULATE(SUM(MarketTable[Values]), MarketTable[Market Segement] = "Other")
var OtherVAluesCount = AllExceptOTher-OtherValues
return SWITCH(TRUE(),
Market = "Other", OtherVAluesCount,
NOT(HASONEFILTER(MarketTable[Market Segement])),OtherValues+OtherVAluesCount,
SUM(MarketTable[Values]))

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi miriammmattar ,

     

    If you want to calcualte the sum of other markets for each market, please simply try:

    Other = CALCULATE(SUM('Table'[Values]),FILTER(ALL('Table'),[Market Segment]<>MAX('Table'[Market Segment])))

     

    Or it's not your expected output, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi miriammmattar ,

     


    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Best Regards,
    Eyelyn Qin