cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zach_TCF Frequent Visitor
Frequent Visitor

Difference filter instead of calculating differences

I recognize the value of the calculate formula, but am wondering if there's an optimal way to calculate the difference based on specific filtering critera for a data column and then dynamically display this 'filtering difference' methodolology for many other data columns. 

 

As an example, each of my data columns includes rows of data with 'Current Forecast' and 'Prior Forecast' detail.  I would like to have predefined 'Current Forecast vs. Prior Forecast' and 'Current Forecast vs. Plan' measures that can calculate this difference based on the data column(s) that I choose to display (such as Ratio 1) in my Power BI visuals.  I would never need to calculate differences horizontally from 2 data columns (such as ratio 1 vs ratio 2).  Below is a sample screenshot of the data structure that envision. 

 

Based on my understanding and use of the the calculate formula, the basic formula locks you into a data column the you specify, and therefore I would need to create 6 different calculate measures just to display Current Forecast vs Prior Forecast for the 3 Ratios and 3 Stat data columns.

 

SampleData.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Difference filter instead of calculating differences

Hi Zach,

 

I found a new solution without changing your data structure. Please download the demo in the attachment. 

1. Create a new table. 

Items	               No.
Widget Margin	        1
Widget Waste	        2
Survey Satisfaction	3
Widgets Produced	4
Meetings	        5
Calls	                6

2. Create two measures.

Current Forecast vs. Prior Forecast New =
SWITCH (
    SELECTEDVALUE ( 'Table3'[No.] ),
    1, CALCULATE (
        SUM ( Table2[Widget Margin] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widget Margin] ), Table2[Scenario] = "Prior Forecast" ),
    2, CALCULATE (
        SUM ( Table2[Widget Waste] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widget Waste] ), Table2[Scenario] = "Prior Forecast" ),
    3, CALCULATE (
        SUM ( Table2[Survey Satisfaction] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE (
            SUM ( Table2[Survey Satisfaction] ),
            Table2[Scenario] = "Prior Forecast"
        ),
    4, CALCULATE (
        SUM ( Table2[Widgets Produced] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE (
            SUM ( Table2[Widgets Produced] ),
            Table2[Scenario] = "Prior Forecast"
        ),
    5, CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Current Forecast" )
        - CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Prior Forecast" ),
    6, CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Current Forecast" )
        - CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Prior Forecast" ),
    99999
)
Forecast vs. Plan New =
SWITCH (
    SELECTEDVALUE ( 'Table3'[No.] ),
    1, CALCULATE (
        SUM ( Table2[Widget Margin] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widget Margin] ), Table2[Scenario] = "Plan" ),
    2, CALCULATE (
        SUM ( Table2[Widget Waste] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widget Waste] ), Table2[Scenario] = "Plan" ),
    3, CALCULATE (
        SUM ( Table2[Survey Satisfaction] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Survey Satisfaction] ), Table2[Scenario] = "Plan" ),
    4, CALCULATE (
        SUM ( Table2[Widgets Produced] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widgets Produced] ), Table2[Scenario] = "Plan" ),
    5, CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Current Forecast" )
        - CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Plan" ),
    6, CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Current Forecast" )
        - CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Plan" ),
    99999
)

3. Now you can format the old table as you wish.

Difference-filter-instead-of-calculating-differences2

 

Best Regards,
Dale

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

6 REPLIES 6
Zach_TCF Frequent Visitor
Frequent Visitor

Dynamic Variance Calculations across factual data columns

I have approximately 50 columns of unique factual data and am wondering if there's a way to create variance calculations that can be used across each of these columns.  The variance calculations would be created based off of the dimensional attributes such as Current Forecast versus Prior Forecast, or Business A vs Businss.

 

SampleData.png

 

 

 

v-jiascu-msft Super Contributor
Super Contributor

Re: Difference filter instead of calculating differences

Hi @Zach_TCF,

 

Can you share a sample of the ORIGINAL data, please?

1. What are the 3 Ratios and 3 Stat? Are they columns or measures?

2. What's the expected result?

 

Best Regards,
Dale

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

Re: Difference filter instead of calculating differences

Hi @v-jiascu-msft,

 

Thanks for your questions. I'm not able to share the original data, but below is fake data to better visualize the question. As you can see, the 6 data columns (3 ratios and 3 stat) are each unique. My Goal is to have 1 calculation 'Current Forecast vs. Prior Forecast' that I can use across all 6 columns, while being able to filter on the month or business (Bus. A or Bus. B). I would like a second calculation to display Current Forecast vs. Plan.

 

SampleData-1.png

v-jiascu-msft Super Contributor
Super Contributor

Re: Difference filter instead of calculating differences

Hi @Zach_TCF,

 

I would suggest you transform the data structure that makes the calculation easily. Please download the demo in the attachment.

Current Forecast vs. Prior Forecast =
SUM ( Table1[Current Forecast] ) - SUM ( Table1[Prior Forecast] )
Forecast vs. Plan = sum(Table1[Current Forecast]) - sum(Table1[Plan])

Difference-filter-instead-of-calculating-differences

 

Best Regards,
Dale

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

Re: Difference filter instead of calculating differences

Hi @v-jiascu-msft,

 

Thank you for your feedback.  As one follow-up to your solution, do you have any recommendations if I wanted to display 'Widget Margin' and 'Widget Waste' as a percent?  It's my understanding that the data for these attributes would have to be reflected in separate data columns if I wanted to display them as at a % instead of the decimals that you've defined for these 3 data columns.  My goal is to limit the number of variance columns that are created, but have the flexibility to display certain attributes as different data types (such as %'s).

 

Thanks,

 

Zach

v-jiascu-msft Super Contributor
Super Contributor

Re: Difference filter instead of calculating differences

Hi Zach,

 

I found a new solution without changing your data structure. Please download the demo in the attachment. 

1. Create a new table. 

Items	               No.
Widget Margin	        1
Widget Waste	        2
Survey Satisfaction	3
Widgets Produced	4
Meetings	        5
Calls	                6

2. Create two measures.

Current Forecast vs. Prior Forecast New =
SWITCH (
    SELECTEDVALUE ( 'Table3'[No.] ),
    1, CALCULATE (
        SUM ( Table2[Widget Margin] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widget Margin] ), Table2[Scenario] = "Prior Forecast" ),
    2, CALCULATE (
        SUM ( Table2[Widget Waste] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widget Waste] ), Table2[Scenario] = "Prior Forecast" ),
    3, CALCULATE (
        SUM ( Table2[Survey Satisfaction] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE (
            SUM ( Table2[Survey Satisfaction] ),
            Table2[Scenario] = "Prior Forecast"
        ),
    4, CALCULATE (
        SUM ( Table2[Widgets Produced] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE (
            SUM ( Table2[Widgets Produced] ),
            Table2[Scenario] = "Prior Forecast"
        ),
    5, CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Current Forecast" )
        - CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Prior Forecast" ),
    6, CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Current Forecast" )
        - CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Prior Forecast" ),
    99999
)
Forecast vs. Plan New =
SWITCH (
    SELECTEDVALUE ( 'Table3'[No.] ),
    1, CALCULATE (
        SUM ( Table2[Widget Margin] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widget Margin] ), Table2[Scenario] = "Plan" ),
    2, CALCULATE (
        SUM ( Table2[Widget Waste] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widget Waste] ), Table2[Scenario] = "Plan" ),
    3, CALCULATE (
        SUM ( Table2[Survey Satisfaction] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Survey Satisfaction] ), Table2[Scenario] = "Plan" ),
    4, CALCULATE (
        SUM ( Table2[Widgets Produced] ),
        Table2[Scenario] = "Current Forecast"
    )
        - CALCULATE ( SUM ( Table2[Widgets Produced] ), Table2[Scenario] = "Plan" ),
    5, CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Current Forecast" )
        - CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Plan" ),
    6, CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Current Forecast" )
        - CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Plan" ),
    99999
)

3. Now you can format the old table as you wish.

Difference-filter-instead-of-calculating-differences2

 

Best Regards,
Dale

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 300 members 3,385 guests