Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Icon for Helper V rankHelper V
2 years ago
Solved

Display total when a quarter is selected

Hello dear members.

 

I need your help

 

I have a matrix displaying the products in the rows,  the quarters in the columns and the sales in values.  I have enabled the row and column totals and at the end of the columns I have the Full year.

I have also a slicer by quarter. 

when a specific quarter is selected the full year shows the selected quarter. I need to display the sales of the selected quarter and in the total to maintain the FY total. 

For example:

                 Q1   Q2   Q3   Q4    FY

Product1   100  100  100  100  400

 

Current results:

                 Q1   FY

Product1   100  100

Desired result: ( when Q1 is selected)

                 Q1    FY

Product1   100  400

Any ideas?

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Dimitris_Kats 

     

    Please try this:

    I create a set of sample:

    Then add a measure:

    MEASURE =
VAR _currentProduct =
    SELECTEDVALUE ( 'Table'[Product] )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Quarter] ),
        SUM ( 'Table'[Values] ),
        CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Product] = _currentProduct )
        )
    )

    Finally, add a matrix. the result is as follow:

     

     

    Best Regards

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Dimitris_Kats 

     

    Please try this:

    I create a set of sample:

    Then add a measure:

    MEASURE =
VAR _currentProduct =
    SELECTEDVALUE ( 'Table'[Product] )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Quarter] ),
        SUM ( 'Table'[Values] ),
        CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Product] = _currentProduct )
        )
    )

    Finally, add a matrix. the result is as follow:

     

     

    Best Regards

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

  • Dimitris_Kats's avatar
    Dimitris_Kats
    Icon for Helper V rankHelper V
    2 years ago

    Thank you so much Anonymous 

    I had to modify the code but it worked. Thank you so much 😊