Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dimitris_Kats
Helper V
Helper V

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?

1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
Community Support

Hi @Dimitris_Kats 

 

Please try this:

I create a set of sample:

vzhengdxumsft_0-1717723919253.png

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:

vzhengdxumsft_1-1717724015351.pngvzhengdxumsft_2-1717724025445.pngvzhengdxumsft_3-1717724033265.png

 

 

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.

View solution in original post

2 REPLIES 2
Dimitris_Kats
Helper V
Helper V

Thank you so much @v-zhengdxu-msft 

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

v-zhengdxu-msft
Community Support
Community Support

Hi @Dimitris_Kats 

 

Please try this:

I create a set of sample:

vzhengdxumsft_0-1717723919253.png

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:

vzhengdxumsft_1-1717724015351.pngvzhengdxumsft_2-1717724025445.pngvzhengdxumsft_3-1717724033265.png

 

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors