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.
Allexcept issues in a herarchy
Hello to everyone, hoping you're doing great. Have you ever had an issue with Allexcept ? Let me explain my problem: In this table, being specific the column "$ Var to Program Avg", I'm using and ALLEXCEPT to get the avg of the values by program in "Unit Calculation" (row 4 equal to 5.36) less (-) Avg Calculation = $ Var to program Avg (5.48 - 5.36 = 0.11). This calculation is good, the issue is that at the program level ("10.00-IN Foxtail Fern"), the value is equal to 0.00 when I whould like to have the sum of the variance at the program level in $ Var to program Avg to sort it and know which one should we look first (There are too many programs). This is an example: Unit Calculation This is the $ Var to program Avg:Few Slicers to work on measures and few are not
Hi, I am facing an issue with creation of DAX. I have got a request from the client where we have a slicer of an Item and Provider at the top of dashboard. I have to show all to KPI for that Item and Provider selected. Further I have all the other dimension filters like Country, City, Corporate group etc. Then I have to create a DAX wherein I have to find the median of my measure amount of the selected Item and Provider. Another measure I have to create is Median of Rest of the Providers amount that were not selected in that Provider slicer. All the dimension filter should work on the rest of the filters but not on the selected filter. Also, I have to show all the slicers so removing interaction is not helping. I have to write the DAX only. Any type of help would be very much appreciated. Thank you.
calculate market share by year on a line chart
Hello All, I am having some issue to plot a graph of market share per year. I have the following data model: I am trying to represent the following graph: For the bar, it is a simple count formula. And the line shart should be the market share. However, it seems like my formula is not working because when no filters are applied, the line should be at 100 percent for each year and not like on the graph above. Here is my dax for the makert share: Market Share = VAR totalIv = CALCULATE(COUNT('Main Table original'[TVA])) VAR totalMarket = CALCULATE(COUNT('Main Table original'[TVA]), ALLEXCEPT('Main Table original', 'Main Table original'[Year])) Return DIVIDE(totalIv, totalMarket,0) How can I fix my formula to have the line at 100 percent when no filters are applied? ThanksIssues 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!
How to 'lock' a value in when using a Table visual
In my data, I have three Departments - New, Used, and Other. For Other, I need 80% of its Sales Income to be added to New's Sales Income (= New Blended Income), and 20% of its Sales Income to be added to Used's Sales Income (= Used Blended Income). I am wanting to present a 2x2 table: Blended Sales Income by Department, with only New and Used departments showing. All attempts to do this filter out the Other component in the New and Used rows. How can I effectively 'lock' the Other component in so that it is not removed by other filtering? I trust it has something to do with KEEPFILTERS, ALLEXCEPT or the like, but can't quite get it working. Thank you for your assistance
REMOVEFILTERS/ALLEXCEPT not removing page level and report level filters
Hi, I have a measure (2 variants) as below to calculate the sum of a column for all calendar periods. Measure = CALCULATE( SUM('FACT ATS'[Planned Quantity Basic Units]), ALLEXCEPT('DIM Bega Calendar', 'DIM Bega Calendar'[Fiscal Period])) Measure = CALCULATE( SUM('FACT ATS'[Planned Quantity Basic Units]), REMOVEFILTERS('DIM Bega Calendar'[REL MTD/LM/NM/MTD YAGO/LM YAGO])) And I have a page level filter applied to the [REL MTD/LM/NM/MTD YAGO/LM YAGO] column of the calendar table to select the current period (202309). And I'm trying to display a table with the sum for all periods. However, the measure below is not removing the period filter and shows data only for the current period. What am I doing wrong here?
