cancel
Showing results for
Did you mean:
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
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 =
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.
3 REPLIES 3
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 =
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.
New Member

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. 🙂

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 !!

Announcements