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
yossil
Frequent Visitor

Assemble annual report from two or more slicers combined

Hi 🙂

 

If I want to assemble a report from two or more sets of slicers, for example:

A financial report that presents annual data (January - December) and will be something like that:

January - March – Actual Data

April - June – Forecast

July - December - Budget

How can I reach the desired outcome using multiple slicers?

 

i.e.  there is a table called " Reporting Cycle" (1 for "Budget", 2 for "Forecast", 3 for "Actual" ect..)

and there is a table called "Months" (1, 2 ,3 and so on..)

 

If I'll choose "budget" on reporting cycle slicer, I will get only the budget for the entire year

And if I'll choose "forecast" or "actual" on reporting cycle slicer, I will receive the same

 

What I need is a combination, which means

Actual for January to March ((months between 1 and 3) and (Reporting Cycle = 3))

Forecast only for April to June ((months between 4 and 6) and (Reporting Cycle = 2))

And budget only for July-December ((months between 7 and 12) and (Reporting Cycle = 1))

 

Any idea ?

1 ACCEPTED SOLUTION

Hi @yossil

 

You may refer to this sample file and check if measure Combination2 matches your request.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
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

4 REPLIES 4
v-cherch-msft
Employee
Employee

Hi @yossil

 

You may refer to below measure. If it is not your case, please let me know and share your data sample.

combination =
SWITCH (
    SELECTEDVALUE ( Report[Reporting Cycle] ),
    3, CALCULATE (
        SUM ( Report[Values] ),
        FILTER ( 'Calendar', 'Calendar'[Months] IN { 1, 2, 3 } )
    ),
    2, CALCULATE (
        SUM ( Report[Values] ),
        FILTER ( 'Calendar', 'Calendar'[Months] IN { 4, 5, 6 } )
    ),
    1, CALCULATE (
        SUM ( Report[Values] ),
        FILTER ( 'Calendar', 'Calendar'[Months] IN { 7, 8, 9, 10, 11, 12 } )
    )
)

1.png

 

Regards,

Cherie

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

Hi @yossil

 

You may refer to this sample file and check if measure Combination2 matches your request.

 

Regards,

Cherie

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

@v-cherch-msft

 

thanks ! that works and it awsome !

Hi and thanks for replying.

your'e proposal is actually hardcoding the combination

I want this to be dynamic as year goes.

 

attached screenshot, each color represent 2 slicers working combined as "AND" argument

the question is How do we make each color to interact with other color as "OR" argument

to get final result as you can see from right.

 

thanks.

 

CombiningSlicers.jpg

 

 

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.