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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerbityro
Helper II
Helper II

chose a certain value and show it for every row

hi all,

 

i have a table with years/quarters and total sales like

 

2019   4    111

2018   4    123

2017   4    145

2016   4    166

2015   4    178

 

on page 1 i have the slicer YEAR = 2019 and QUARTER = 4. how can i choose the total sales for the last year (YEAR -5) and show it for every year like

 

2019   4    178

2018   4    178

2017   4    178

2016   4    178

2015   4    178

 

maybe there are some simple ways?

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

Hi @powerbityro 

Create table2

Table 2 = SUMMARIZE('Table','Table'[year],'Table'[quarter])

Create a measure

Measure =
IF (
    MAX ( 'Table'[year] )
        >= SELECTEDVALUE ( 'Table 2'[year] ) - 4
        && MAX ( 'Table'[year] ) <= SELECTEDVALUE ( 'Table 2'[year] )
        && MAX ( 'Table'[quarter] ) = SELECTEDVALUE ( 'Table 2'[quarter] ),
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[year]
                = SELECTEDVALUE ( 'Table 2'[year] ) - 4
                && 'Table'[quarter] = SELECTEDVALUE ( 'Table 2'[quarter] )
        )
    )
)

Capture4.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
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

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @powerbityro 

Create table2

Table 2 = SUMMARIZE('Table','Table'[year],'Table'[quarter])

Create a measure

Measure =
IF (
    MAX ( 'Table'[year] )
        >= SELECTEDVALUE ( 'Table 2'[year] ) - 4
        && MAX ( 'Table'[year] ) <= SELECTEDVALUE ( 'Table 2'[year] )
        && MAX ( 'Table'[quarter] ) = SELECTEDVALUE ( 'Table 2'[quarter] ),
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[year]
                = SELECTEDVALUE ( 'Table 2'[year] ) - 4
                && 'Table'[quarter] = SELECTEDVALUE ( 'Table 2'[quarter] )
        )
    )
)

Capture4.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlB
Super User
Super User

Hi @powerbityro 

1. select years 2015 to 2019 and Quarter 4 in the slicers,

2. place Year and Quarter in a table visual

3. Create this measure and place it in the visual

Measure = CALCULATE(SUM(Table1[Sales]), Table1[Year] = 2015)

 

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

  

Hi AlB,

i have only a single slicer YEAR = 2019 and QUARTER = 4

 

1) the table should show the last 5 years with the columns YEAR/QUARTER/SALES

2) and i need an another column with the sales of the last year (in example 2015) for every row

 

i need a dynamic solution on one page without to create a new table because i work in a live connection (not possible to create a table).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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