Create Measure not Affected by Filter

[Anonymous]

Hi,

I have created a measure that outputs the ratio of the number of hours worked to the number of guests in a hotel location. I would like the create a second measure that performs the same calculation but for the company as a whole, and therefore should not be affected by filters (such as location). I will use the two measures in a line chart that will show one line for the selected location and one line for the company. I have tried to modify my code using the "ALL" function, but the output values are not correct. Below is my code for the location specific meaure and the version (non-working) for the company measure that should not be affect by filters.

Thank you for your help. 

Hour_Guest_Ratio_Location = divide(
                                Calculate(sum(Labor[DailyLabor]),filter(Labor,Labor[Labor_report_description]="Total Paid (D + I)")),                      
                                Calculate(sum(vDSPDdaily_PowerBI[DailyValueRound] ),
                                    filter(Category,Category[CategoryLabel]="Guests in House"
                                    ||Category[CategoryLabel]="Guests in Transit"
                                    ||Category[CategoryLabel]="Guests in Limbo")))

Hour_Guest_Ratio_Company = divide(
                                Calculate(sum(Labor[DailyLabor]),filter(ALL(Labor),Labor[Labor_report_description]="Total Paid (D + I)")),                      
                                Calculate(sum(vDSPDdaily_PowerBI[DailyValueRound] ),
                                    filter(ALL(Category),Category[CategoryLabel]="Guests in House"
                                    ||Category[CategoryLabel]="Guests in Transit"
                                    ||Category[CategoryLabel]="Guests in Limbo")))

[Anonymous]

Hi,

I have created a measure that outputs the ratio of the number of hours worked to the number of guests in a hotel location. I would like the create a second measure that performs the same calculation but for the company as a whole, and therefore should not be affected by filters (such as location). I will use the two measures in a line chart that will show one line for the selected location and one line for the company. I have tried to modify my code using the "ALL" function, but the output values are not correct. Below is my code for the location specific meaure and the version (non-working) for the company measure that should not be affect by filters.

Thank you for your help. 

Hour_Guest_Ratio_Location = divide(
                                Calculate(sum(Labor[DailyLabor]),filter(Labor,Labor[Labor_report_description]="Total Paid (D + I)")),                      
                                Calculate(sum(vDSPDdaily_PowerBI[DailyValueRound] ),
                                    filter(Category,Category[CategoryLabel]="Guests in House"
                                    ||Category[CategoryLabel]="Guests in Transit"
                                    ||Category[CategoryLabel]="Guests in Limbo")))

Hour_Guest_Ratio_Company = divide(
                                Calculate(sum(Labor[DailyLabor]),filter(ALL(Labor),Labor[Labor_report_description]="Total Paid (D + I)")),                      
                                Calculate(sum(vDSPDdaily_PowerBI[DailyValueRound] ),
                                    filter(ALL(Category),Category[CategoryLabel]="Guests in House"
                                    ||Category[CategoryLabel]="Guests in Transit"
                                    ||Category[CategoryLabel]="Guests in Limbo")))

[Greg_Deckler]

Really difficult to say without sample data and sample output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But perhaps try something like:

Hour_Guest_Ratio_Company = 
VAR __laborTable = ALL('Labor')
VAR __categoryTable = ALL('Category')
VAR __numerator = SUMX(FILTER(__laborTable,[Labor_report_description]="Total Paid (D + I)"),[DailyLabor])
VAR __denominator = SUMX(FILTER(__categoryTable,
   [CategoryLabel]="Guests in House"
   ||[CategoryLabel]="Guests in Transit"
   ||[CategoryLabel]="Guests in Limbo"),[DailyValueRound]
)
DIVIDE(__numerator,__denominator,0)

However I am guessing that the problem lies with the portion in red:

Hour_Guest_Ratio_Company = divide( Calculate(sum(Labor[DailyLabor]),filter(ALL(Labor),Labor[Labor_report_description]="Total Paid (D + I)")), Calculate(sum(vDSPDdaily_PowerBI[DailyValueRound] ), filter(ALL(Category),Category[CategoryLabel]="Guests in House" ||Category[CategoryLabel]="Guests in Transit" ||Category[CategoryLabel]="Guests in Limbo")))

That seems to be another table where you are not placing an ALL on it so it may be filtered somehow. Again, super difficult without data and knowing what the results should be. 

[Anonymous]

Thank you for the response Greg. As you said, I need to provide you with much more information, especially since I am still very new to DAX and don't yet speak the lanuage. I put together a data set for you, which outlines the different tables and will help explain what I am trying to do; although it looks like I can not upload files. I will send you a PM with a drop box link to an excel file. I also think your initial thought on the table not being filtered is correct. Putting an ALL() around that table resulted in an error - I am still learning how Calculate, All and Filter interact with each other and what they expect. 

To better explain my goal: I want to create a ratio of employee hours to the number of guests in house (dogs) and I want there to be two versions of this ratio: 

1. A version that can be filtered based on the location "LocationInformation [LocationName]" and calendar date "Calendar"
2. A version that can only be filtered based on the calendar date, meaning it calculates all locations together as one number for the company. Basically I do not want the output of the code that I pasted to change when I filter locations. 

Any filtering will be done via slicer. I tried the formula in your prior post and it threw an error related to the Divide syntax (which I do not believe to be the case and I could not figure out how to fix it). 

The formula I posted does work for bullet point #1 (reposted below, note that the filter variables have been changed to match the data I am providing). In a perfect world I would add an AllExcept (LocationInformation) in the Filter arguments but that ends in an error. 

Relationships: 

Category 1:1 vDSPDdaily_PowerBI

LocationInformation *:1 vDSPDdaily_PowerBI

Calendar 1:* vDSPDdaily_PowerBI

Labor *:1 LocationInformation

Labor *:1 Calendar

Thanks for the help. Please let me know if I can provide anything else. 

Hour_Dog_Ratio_Location = divide(
                                Calculate(sum(Labor[DailyLabor]),filter(Labor,Labor[Labor_report_description]="Total Paid (D + I)")),
                                Calculate(sum(vDSPDdaily_PowerBI[DailyValueRound]),
                                   filter(Category,Category[CategoryLabel]="Boarding pets"
                                        ||Category[CategoryLabel]="Daycare pets"
                                        ||Category[CategoryLabel]="Grooming pets"
                                        ||Category[CategoryLabel]="Special services pets"
                                        ||Category[CategoryLabel]="Training pets"))

[Anonymous]

Hi Somail,

I think you could use ALLEXCEPT to remove all the filters applied to the datatable except the filters on the location "LocationInformation [LocationName]" and calendar date "Calendar". And then use the filters for [Category] in CALCULATE to get what you want. 

It should be something like:

CALCULATE(SUM(vDSPDdaily_PowerBI[DailyValueRound]),
ALLEXCEPT(vDSPDdaily_PowerBI,LocationInformation [LocationName],Calendar[Date]),
Category[CategoryLabel]="Boarding pets" ||Category[CategoryLabel]="Daycare pets" ||Category[CategoryLabel]="Grooming pets" ||Category[CategoryLabel]="Special services pets" ||Category[CategoryLabel]="Training pets")