How to create a measure that ignores your current slicer while keeping time intelligence?

[AW11]

Hello, 

I am trying to create a measure that ignores the current slicer on the page and calculates the total revenue of the whole company for each year (2017 through 2022).

The slicer is from the Company table and slices on subsidiaries.

I have the following measure:

Metric_TotalRevenue =

CALCULATE(sum(QRY2UNPIV[Value]),ALL(QRY2UNPIV),QRY2UNPIV[Attribute] = "Revenue", QRY2UNPIV[_Company] = "XYZ Inc.")

This is successful in outputting the total revenue for XYZ inc. while sliced on a different company, however it gives me the cummulative revenue for all years (2017 through 2022) and when I put it in a matrix as the "Values" and the Calendar[Year] as the "Columns", the value does not change and remains the static value of total revenue for all six years. 

Could you please advise?

[v-tangjie-msft]

Hi @AW11 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2)We can create a Calendar table.

CALENDAR = CALENDAR(DATE(2017,1,1),DATE(2022,12,31))

(3) We can create a measure.

Metric_TotalRevenue =
CALCULATE (
    SUM ( QRY2UNPIV[Value] ),
    FILTER (
        'QRY2UNPIV',
        'QRY2UNPIV'[Attribute] = "Revenue"
            && QRY2UNPIV[_Company] = "XYZ Inc."
    )
)

(4) Then the result is as follows.

Best Regards,

Neeko Tang

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

[AW11]

Hello,

Thanks for the response! However, this measure will not work in ignoring the active slicer on the page.

There is a third table (Company table that has business segments) that is not incorporated in your response and if I slice on a business segment the measure will be blank.

If say I have the page sliced on Segment "Orange", the measure will be blank. While instead I want it to still be able to show the total revenue of the consolidated company "XYZ Inc.".

[v-tangjie-msft]

Hi @AW11 ,

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. Thank you.

Best Regards,

Neeko Tang

[AW11]

Hello Neeko,

Your sample data is mostly correct. One small thing is that the data is annual and not by month or day and then you are missing one table with BU Company and Segment. 

Company Name	Segment
XYZ Inc	XYZ Inc
XYZ Inc	East
A	Midwest
B	South

The page is currently sliced on the South Segment to show the financial data (attributes) of Company B South. However, I want to pull the whole company's revenue (XYZ Inc.) when I am sliced on this specific segment to compare Company B South's Expenses to XYZ Inc.'s total revenue.