cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LCCKevO
New Member

Different results dependent on slicer

I have a built financial reports that includes measures for "variance" and "change in variance from last period". The report has slicers for "Financial Year" and "Period". Most of the time these work how I want them to.

 

However, when "Period" is filtered to "1" the "change in variance from last period" calculates as the difference between the last value for the previous year, and the first of the selected year. It should just be the "variance" figure, as at the start of the year there is no variance. I thought the way around this was to use an if function, but when I've tried it doesn't work.

 

The DAX I've tried so far, is as below:

= If ([Period] = "1", "Variance", "Change in Variance")

 

Any suggestions on how I can get this to work?

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @LCCKevO ,

In order to give you a suitable solution, could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

 

In addition, please review the following links and check if they can give you some insights or direction. 

Different Measures Based on Slicer Selection in Power BI

DAX – Calculating the difference between 2 selections on a Slicer

Difference between results based on slicer selection

Variance from Selected Companies = 
VAR summary =
    ADDCOLUMNS (ALLSELECTED(Item_Table[Company]), "cCompany", RANKX(ALLSELECTED(Item_Table[Company]),[Grand Total]))
VAR S1=
    CALCULATE([Grand Total],FILTER(summary,[cCompany]=1))
VAR S2=
    CALCULATE([Grand Total],FILTER(summary,[cCompany]=2))
Return
S1-S2

Best Regards

Community Support Team _ Rena
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
yingyinr
Community Support
Community Support

Hi @LCCKevO ,

In order to give you a suitable solution, could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

 

In addition, please review the following links and check if they can give you some insights or direction. 

Different Measures Based on Slicer Selection in Power BI

DAX – Calculating the difference between 2 selections on a Slicer

Difference between results based on slicer selection

Variance from Selected Companies = 
VAR summary =
    ADDCOLUMNS (ALLSELECTED(Item_Table[Company]), "cCompany", RANKX(ALLSELECTED(Item_Table[Company]),[Grand Total]))
VAR S1=
    CALCULATE([Grand Total],FILTER(summary,[cCompany]=1))
VAR S2=
    CALCULATE([Grand Total],FILTER(summary,[cCompany]=2))
Return
S1-S2

Best Regards

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

Thanks @yingyinr 

The video on Different Measures based on Slicer Selection in Power BI provided an answer to my problem. This is going to be very useful. 🙂

amitchandak
Super User
Super User

@LCCKevO , the way to deal with any nonstandard period

Say on YYYYMM

 

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

or

Period Rank = RANKX(all(Period),Period[Period Start Date],,ASC,Dense)

 

 

measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

 

DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors