Forum Discussion

learningOh's avatar
learningOh
Frequent Visitor
3 years ago
Solved

Measure placed in Table visual not showing total

i have this code that i will like to calculate the revenue earned this year if the particular name did not have any revenue last year but have revenue this year.

if this condition is met, it will show the revenue gained as this year revenue if not 0.
 
the total at the bottom of the table visual should show me the total revenue gained. but for some reason it is just showing 0. how do i resolve this?


Rev Gains =
if(
    sumx(
        FILTER(
            'FACT',
            [fiscal_year]=year(today())-1),
            [Revenue]) = 0
            &&
            sumx(
                FILTER(
                    'FACT',
                    [fiscal_year]=year(today())
                    ),[Revenue]) <> 0,
    calculate(sum('FACT'[Revenue]),
        FILTER(
            'FACT',
            [fiscal_year] = Year(today()))),0)



Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, learningOh 

     

    You can try the following methods. Add a new measure:

    Measure = 
Var _table=SUMMARIZE('FACT','FACT'[Name],"Value",[Rev Gains])
Return
SUMX(_table,[Value])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

3 Replies

  • Kishore_KVN's avatar
    Kishore_KVN
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hello learningOh 

    Please use SUMX inside Calculate function as well to get the total matching. 

    Rev Gains =
    if(
        sumx(
            FILTER(
                'FACT',
                [fiscal_year]=year(today())-1),
                [Revenue]) = 0
                &&
                sumx(
                    FILTER(
                        'FACT',
                        [fiscal_year]=year(today())
                        ),[Revenue]) <> 0,
        calculate(sumx('FACT','FACT'[Revenue]),
            FILTER(
                'FACT',
                [fiscal_year] = Year(today()))),0)
     
    If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
    • learningOh's avatar
      learningOh
      Frequent Visitor
      3 years ago

      Thank you for helping me on this.

      i have changed to the same formula that you provided. but the result is the same. the table total is still missing. 

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, learningOh 

     

    You can try the following methods. Add a new measure:

    Measure = 
Var _table=SUMMARIZE('FACT','FACT'[Name],"Value",[Rev Gains])
Return
SUMX(_table,[Value])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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