Forum Discussion

ferdiakinci's avatar
ferdiakinci
Frequent Visitor
3 years ago
Solved

pivot table week month year sum

Hello,

 

I want to calculate weekly, monthly, annual totals in pivot table. When I select week 8 from the filter section at the top, I want the monthly data to be collected until the 8th week of the relevant month, and to calculate the entire sum up to the 8th week in the annual field. how can i do this?
I used the FİLTER(ALL) command, but here it writes the total in the column to all rows. It does not perform row-based operations. can you help me?

example:

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ferdiakinci ,

    Your error message clearly tells you the error. You have a table called _month, so you can't define a variable. you can either change the name of the table _month, or you can change the name of the variable _month.

     

    There is an error in this formula, please note the correction.

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Please provide some more details like data or just a snippet of data and more clearer requirements.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ferdiakinci ,

    Please  have a try.

    Create three tables about month, year and week. Then put them into different slicers.

    _month=summarize(table,table[month])
    _year=summarize(table,table[year])
    _week=summarize(table,table[week])

    Then create a measure.

    measure =
VAR _month1 =
    SELECTEDVALUE ( _month[month] )
VAR _year1 =
    SELECTEDVALUE ( _year[year] )
VAR _week1 =
    SELECTEDVALUE ( _week[week] )
VAR _month =
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( ALL ( table ), table[month] <= _month1 )
    )
VAR _year =
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( ALL ( table ), table[year] <= _year1 )
    )
VAR _week =
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( ALL ( table ), table[week] <= _week1 )
    )
RETURN
    IF (
        _month1 = BLANK ()
            && _year1 = BLANK ()
            && _week1 <> BLANK (),
        _week,
        IF (
            _month1 = BLANK ()
                && _year1 <> BLANK ()
                && _week1 = BLANK (),
            _year,
            IF (
                _month1 <> BLANK ()
                    && _year1 = BLANK ()
                    && _week1 = BLANK (),
                _month,
                BLANK ()
            )
        )
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    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
        3 years ago

        Hi ferdiakinci ,

        Your error message clearly tells you the error. You have a table called _month, so you can't define a variable. you can either change the name of the table _month, or you can change the name of the variable _month.

         

        There is an error in this formula, please note the correction.

        How to Get Your Question Answered Quickly 

         

        If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

         

        Best Regards
        Community Support Team _ Rongtie

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