Calculate Region revenue in a Country table
Hi I require the Dax to be able to calculate the region revenue instead of the country revenue in a matrix table by data set is as follows the table i'm trying to create in power BI is something like this where i display both the country Rev and the Region rev next to each other which i have managed to do with the following dax. Region Rev = CALCULATE(SUM('Dataset 1'[Revenue]),FILTER(FILTER(ALL('Dataset 1'),'Dataset 1'[Region]=SELECTEDVALUE('Dataset 1'[Region])),'Dataset 1'[Year]=SELECTEDVALUE('Dataset 1'[Year]))) However i cannot get it to calculate by industry and it is just giving me the total rev for the region, rather then by Industry by for that region. in theory all i want to do is stop the measure from filtering on Country and instead filter on the region for that country when it is displaying any values any help would be much appreciated!Percentile of a table stored in a variable
Hi all, I would like to detect outliers in my daily data report by using the percentile function. I have a detail table (table 1) and I want to calculate the percentile of the second table which shows the tickets per day. If I create a fixed calculated table I have to add "ALL / AllExcept" function inside Percentile function: Percentile = PERCENTILEX.EXC (ALL(TicketsByDate), TicketsByDate[Count_Tickets], .90 ) I need to apply external filters and I don't want to rely on the All/AllExcept function. For this reason, I want to store my calculated table in a variable. The problem is that I can't calculate the percentile of this calculated table in a variable. I get the following error: "If k is not a multiple of 1/(n + 1), PERCENTILEX.EXC will interpolate to determine the value at the k-th percentile." Can someone help me? Thanks!!!
Current vs Previous Quarter Sales
Hi All, I have the following tables Table : Sales Table: Employee There exists a 1 to many relationship from Employee to Sales. I want to display current vs previous quarter sales by employee in a table visual. When nothing is selected in Quarter slicer, CQ Sales should show sales for 2021 Q4. But it is not working. Measure PQ Sales should display the previous quarter sales for the corresponding employee. I have written the measures as follows Please help me to fix this
Different results of All and Summarize function on a column both combined with Keepfilters
Hello, I am new to DAX and have recently completed the evaluation concepts. I am wondering why following yield different result from the other function. Here is the link to access pbix on which i am working on. https://drive.google.com/file/d/17uSqXKk-zgmFlRF9XLDnYpsIJZQxhemt/view?usp=sharing DAX 1 DAX 2 I just replaced All filter with Summarize function. I understand that when All function is used with a column reference then it yields unique values of the column. While Summarize function (when applied on a column) provides unique values. Both these functions being filter parameters of Calculate functions work in original filter context environment. The way i understand keepfilters works, it intersect the values of columns in original filter context and the values derived of the same column in keepfilters function. Ideally, i was expecting them to yield the same result. However, in this case as we see, it is yielding different output. Thank you for your time in going through the question and helping me out !ALL() leads to wrong calculations
Hello all, I am experiencing some problems with the ALL() function. In a few words, I am want to display the total sales + market shares for a few item from field 1. - To do so, I am filtering my table to display only the wanted items. - Also, in the DAX formula for my Market Share, I used the ALL() function for the denominator to take into account all the items in field 1, and not only the one filtered. Here it is Denominator = CALCULATE ( SUM ( TABLE[Sales] ), ALL ( TABLE[field 1] ) ) => However, I noticed that this calculation is lower than it should be, as if Power BI was not taking into account all the lines in my table. => Randomly, I added an item in my table (which accounts for a large number of lines in my TABLE). Then the calculation was correct as if this forced Power BI to take into account all the lines in my TABLE when calculating. I looked through the Microsoft documentation (https://docs.microsoft.com/fr-fr/dax/all-function-dax), it did not help since it does not seem to be a problem of "Dax auto-exist' (https://www.sqlbi.com/articles/understanding-dax-auto-exist/). Many thanks for your help !!
All Selected is not working properly, I have to include another Column
Hello, I have a fact table that has multiple colums: Item,Period_Name,Period_ID,Sales Period name is like Jan 2022 , Feb 2022 etc Period_ID is like 202201 , 202202 etc I created one slicer in the dashboard that has "Period_Name" Field and created 1 grid in the dashboard in which I want to include 4 columns: Item, Period, Sales Value, Sales value (for the all selected periods) , Sales Value (for all the periods selected and unselected) I created the measures like this Total Value Sales = CALCULATE(SUM(Fact_Sales[Value Sales])) Total Value Sales (Selected Period) = CALCULATE([Total Value Sales], ALLSELECTED(Fact_Sales[Period])) Total Value Sales (Ignore Period) = CALCULATE([Total Value Sales], ALL(Fact_Sales[Period])) But I got wrong results like the image I tried so much to solve it and solved successfully by changing in the measures Total Value Sales (Selected Period) = CALCULATE([Total Value Sales], ALLSELECTED(Fact_Sales[Period]),ALLSELECTED(Fact_Sales[PERIOD_ID])) Total Value Sales (Ignore Period) = CALCULATE([Total Value Sales], ALL(Fact_Sales[Period]),ALL(Fact_Sales[PERIOD_ID])) As you can see, numbers now are reflecting correctly... but I am unable to understand the behaviour, why it didn't work although I am not using this Period_ID column anywhere? also I wanted to create another sheet with dummy data to upload here to the community to make things easier, but surprisingly it worked successfully without adding the second condition. Does anyone have a clue? Thanks. John Elmasry
ALLEXCEPT and ALL function does not work as I expected in DAX
Hi Everyone, I'm trying to use All/Allexcept functions in some calculations for some case scenarios. I was working with Qlik & QlikSense before and this is easly applicable on Qlik but I could not manage to get this done on PowerBI. This may not be applicable on powerbi or I may not achieve to do it. Eveything explained on the last page(ALL & ALL EXCEPT Examples), but I will give summary here again. (PBIX file : GoogleDriveLink) Page : ALL & ALL EXCEPT Examples What I'm trying to do is, I want to put a few measure on a table and when I make a selection from a field, I want only some calculations get affected from that selection, and some others don't. For example, I will put Master Date as dimension and 3 different calculation on the table. I will make a selection from Service Category field and only 1th. measure will be affected from that selection and others don't. Then I will make another selection from Availability field, then 2.th measure will be affected from the selection etc... I have 4 tables. Appoinment, Marketing, Utilization, Availability. All those 4 tables merged into appointment table. Measure 1: Calculation gets affected from only determined fields in the all except function. Test Appointment Qty = CALCULATE(sum(Appointment[m_appointment_qty]) ,ALLEXCEPT(Appointment ,Appointment[dim_country_id] ,Appointment[dim_country] ,Appointment[dim_master_date] ,Appointment[dim_service_category] ,Appointment[dim_promise_met]) ,Appointment[table_type]="Appointment" ,Appointment[dim_calculation_type]="Original" ) Measure 2: Calculation will not be affected from the determined field selections. Test2 Appointment Qty = CALCULATE( sum(Appointment[m_appointment_qty]) ,ALL(Appointment[data_flag]) ,ALL(Appointment[Availability]) ,ALL(Appointment[dim_day_diff]) ,ALL(Appointment[cleaner_id]) ,Appointment[table_type]="Appointment" ,Appointment[dim_calculation_type]="Original" ) Those calculations works fine if I don't put any dimension into the table but it does not work if I put any dimension. I tried it with and without FILTER function in the calculation but I could not manage this to work with FILTER function. When I put FILTER in dax formula, FILTER function does not allow me to put multiple dimensions to tell the calculation not to affect from any selection. More detailed explanation and use cases are on the page in PBIX file. I'd be so happy if you see the file and help me to write the correct DAX formula. Thanks for your support already. Table 2 Works fine but Table 1 will not be shown if I make any selection. I attached the uses cases as well.
Correct Total using ALL() whilst also having blanks filtered out of visual?
Hi I'm hoping someone may have a quick solution to a problem i'm encountering whilst trying to provide % of total in a bar chart. The screenshot below shows a dummy dataset for customer feedback replicating the issue i'm encountering. The data spans over 3 days (20th Oct to 22 Oct), with a total of 25 customers, 9 of which opted not to give feedback. I'm needing to present this data as a % broken out by feedback score. I have used the 'feedback%' measure shown below, which leverages the ALL() function to clear the feedback_rating filter, providing a total customer count. You can see from the 2nd bar chart that this works when the blank feedback rating option is included in the visual. Example feedback score of 1 has 5 customer entries, 5/25 = 20%. I have been asked to remove blanks from the visual. When these are removed however, the % are incorrect as can be seen within the red bar chart, a feedback score of 1 now has a % distribution of 26%. The reason appears to be due to the Date slicer which is required in my real dashboard. As all the customers on 21st October did not leave feedback, I believe when the blanks are removed from the visual, all of these rows are removed from the underlying data, creating a new customer total of 19, resulting in incorrect %'s, feedback score of 1 now becoming 26% (5/19) as mentioned above. Any ideas on how to keep these values in the total whilst also keeping the blank entries out of the visual? Ideally I would like to solve it within a DAX measure as my existing dashboard already has a relatively complex data model, however I appreciate it may require a helper table of some sort. feedback% = distinctcount(Sheet1[customer_id]) / CALCULATE(DISTINCTCOUNT(Sheet1[customer_id]),ALL(Sheet1[feedback_rating])) Thanks for reading.
