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
Returning a list of distinct rows that have been deselected by a slicer/filter
Hi All, I have a tooltip on all pages in my report that shows what the user has selected/not selected on slicers on the landing page. It works perfectly outside of the not selected "list", which currently concatenates all the "not selected" rows in the table, rather than just the distinct "not selected" rows in the table. Part of the dax is below Selection RAG = VAR SelectedValues = VALUES ( 'Account'[Account Group] ) VAR ConcatTable = CONCATENATEX ( CALCULATETABLE ( SelectedValues ), 'Account'[Account Group], ", " ) VAR ConcatTableExcept = CONCATENATEX ( CALCULATETABLE ( Account, Except(All(Account[Account Group]), SelectedValues)), 'Account'[Account Group], ", " ) The VAR ConcatTable works fine and concatenates values that have been selected in the slicer and is used when the number of selected values in the slicer is a low number The VAR ConcatTableExcept is used when the number of deselected values in the slicer is low and number of selected values is high, except the problem is it returns all the rows in the table that havent been selected in the slicer rather than the distinct values that have not been selected. I have tried to wrap the All inside of a distinct function like this Distinct(All(Account[Account Group])) and various other means but have not cracked the code yet. Some example screenshots below. When Sales Development is not selected (All rows containing Sales Development) When only Sales Development is selected (correct) Any help would be greatly appeciated
ALL() and REMOVEFILTER() Don't Work Correctly on My Report
Hi, I have a problem that showing Lifetime amount by using ALL() or REMOVEFILTERS() functions on my report. I have created a new date table as slicer that implies Yesterday, Last 7 Days, Same Day Last Month, All, Custom etc., and it works perfect. But when I selected any slicer rather than "All", Lifetime Revenue does not show correct amount. Since the new slicer table consists duplicate dates, the cross-filter direction on relationship is not single as below. The dax query, I've used is to get Life Time Revenue: Revenue_Lifetime = CALCULATE( SUM(Revenue[Revenue]), ALL('Date Periods'[Date],'Date Periods'[Type]) ) Here is a sample report that shows the problem, https://we.tl/t-Q0SBtDHTFn Thank you, Kisa
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.
create a measur with a filter ignoring filter on the page level
i have created a measure that calculates a oercentage with a filter. i have the same filter in my table visual but i want to ignore the filter that is in the visual and apply the filter in the measure. example: accessories achievement = calculate(achievement * .1 , category[type]= accessories, all(category[type)) my table visual should show the below category type core achievement accessories achievement gross aad core 94 8 renewal core 90 7 note: there's a filter on page level for the column type=core. i want to show accessories achievemnet with type= accessories ignoring this page level filter= core. currently with the measure i created above, accessories achievement is showing blank.Issues with level of detail calculation using a relationship model
I have 2 tables related. Table A (Maestro de partes) is the dimensional table, where each SKU code (NOPARTE) and brand group (CodMarca) is located. Table B (Liq 3M) is the fact table, stores the transacction at the SKU and date level from each import process in the last 3 months. I need to calculate the sum of the import costs over the FOB factory price at the brand group level. I also need this value to remain the same whether I disaggregate it per SKU or brand group. Here is the formula I'm using considering the filters I need for this calculation: Factor de importacion = CALCULATE( ([Total costos de importacion]/SUM('Liq 3M'[FOB unit liquid]) + 1), FILTER( 'Liq 3M', 'Liq 3M'[Via transporte] = 2 && 'Liq 3M'[Datediff aplicacion] > 0 && 'Liq 3M'[Datediff aplicacion] < 4 ), ALLEXCEPT( 'Maestro Partes', 'Maestro Partes'[CodMarca] ) ) https://1drv.ms/u/s!Ar7k80093sB_gckbSsUJKl9osviTWw?e=VfMKnh This is what I get: This is what I need: Any help will be appreciated!
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 !Estimated total sales based on the mean
Hello community, I have a longer question. I will try to describe the whole thing as clearly as possible. 1) This is my data I use the database ContosoRetailDW_2019. From this I imported the following 2 tables: Fact Sales DimDate Of course, both tables are linked via a 1-to-N relationship. I only imported sales before 8/1/2009. If I have the total sales per year and month displayed in a matrix, this is the picture I get: 2) This what I want to do I would now like to estimate the total sales for the months of August to December 2009, using the mean value of the total sales for the past few months. This is a bit difficult to describe, so I'll show you what I want to do in Excel: xcel) 3) These are my measures 3.1) The measure Total Sales I start by creating the Total Sales measure: Total Sales = SUM(FactSales[SalesAmount]) 3.2) The measure Total sales one month all years Now I need a measure that adds up the total sales of a month over all years. I will use it as a dividend in my closing calculation. For a better understanding, I'll show you again in Excel what I want to calculate: Here is the measure Total sales one month all years: Total sales one month all years = CALCULATE( [Total Sales], ALL(DimDate[CalendarYear]) ) When I drag the measure into the matrix I get this picture: 3.3) The measure Number of total sales one month all years (here are the problems!) Now I need a measure that gives me the number of total sales. So that you know what I mean, I'll show you again in Excel what I want to do: I wrote the following measure Number of total sales one month all years: Number of total sales one month all years = COUNTX( ADDCOLUMNS( SUMMARIZE( ALL(DimDate), DimDate[CalendarYear], DimDate[CalendarMonthLabel] ), "Result", [Total Sales] ), [Result] ) The idea is this: I create a table grouped by year and month with SUMMARIZE and then add a result column with ADDCOLUMNS, in which I calculate the total sales. I then use COUNTX to count how many results I have in my summary table. You can see that I get the correct numbers in the values of the matrix. The result column is also correct. However, I cannot explain the values in the result line. Here the total amount (31) comes out everywhere. 3.4) The measure Estimated total sales Anyway, I'll keep going on. I now create the Estimated total sales measure: Estimated total sales = [Total Sales One month All years] / [Number of Total Sales One Month All Years] 3.5) The measure Total sales or estimated sales Now I create the final measure, which outputs either Total sales or Estimated total sales: Total Sales or estimated total sales = IF( ISBLANK([Total Sales]), [Estimated total sales], [Total Sales] ) I drag it into the matrix and of course the overall results are wrong: It should actually look like this: 4) How are you doing right? Thank you so much for reading this really long text! I am of course aware that the problem lies in the measure Number of Total Sales One Month All Years. Unfortunately I can't find the right solution. Can you help me? Thanks in advance!
