Slow DAX formula to calculate # Orders Delayed with adjustment
Hi I have a datamodel with orderlines and goods receipt. I have written a formula to calculate number of unique orders delayed (in Danish # Ordre Forsinket) based on two what if parameters. The parameter 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat] is used to adjust the delay with X number of days on those orders there are marked as indkøbsbilagsart ZCD or ZLM. The parameter 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat] is used to adjust the delay with X number of days on those orders there are different from indkøbsbilagsart ZCD or ZLM. The data model looks like this and there is 1.4 million rows in the table 'Ordrelinjer' and 2.775 suppliers in the table 'Leverandør': Here is the DAX formula: # Ordre Forsinket = VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat], 0 ) VAR _LeveringstidOevrigeRabat = SELECTEDVALUE( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat], 0 ) RETURN CALCULATE( DISTINCTCOUNT('Ordrelinjer'[Indkøbsordrenummer]), FILTER( ADDCOLUMNS( FILTER( 'Ordrelinjer', 'Ordrelinjer'[Varemodtagelse] = 1 && 'Ordrelinjer'[Afvigelse i dage] > 0 ), "JusteretAfvigelse", 'Ordrelinjer'[Afvigelse i dage] - IF( RELATED('Indkøbsbilagsart'[Indkøbsbilagsart]) IN {"ZCD", "ZLM"}, _LeveringstidRegionslagerRabat, _LeveringstidOevrigeRabat ) ), [JusteretAfvigelse] > 0 ) ) The measure is used in a table visual, but it takes around 33 seconds to refresh the table visual. I need a few more measures in the table, so it will just take longer and longer time to refresh the table. Can I write the DAX formula in a more efficient, clean and performance optimized way? Best regards Morten
Table Visualization by Customer breakdown by Type and Month
Hello Power BI Community Can anyone please advise me on how to resolve this. My use-case is that we have monthly finance transactional data for each month from Jan to May (e.g. Data source is SQL), bringing in the latest data every month. My Datasets each row consists of Month, Customer, Brand, Sales Price, and Volume and Data model consist of DateTable, where the month has one to many relationships with Customer Table. I manage to create DAX showing MTD & YTD calculation for [Volume], [GM], [ARP £/MT]. Please note [Volume], [GM], [ARP £/MT] these are measures created from monthly finance transactional data. I tried Summarise and Add column DAX nothing seems to be working out, may be i'm made some error. DAX Measures for Volume = Sum('Gross Margin'[Volume]) DAX Measure for ARP£/MT = (DIVIDE([Sale Price],[Volume])) My final output would like to plot the data on a column chart by Type (Volume], ARP £/MT], [GM £/T] the columns would be stacked in each month to give the break-down by the customer by Brand. I want to create two table visualization one by MTD and another Cumulative total, without giving the grand total for ARP £/MT (Average Retail Price) & GM £/ MT, something like this (above mentioned format). Appreciate your help in advance.😊
Aggregated values directly at the last rows of TABLE visuals
Hi, Being a newbie requesting help to get the aggregated values below the "total" row generated from TABLE visual. Unable to use MATRIX visual as i need to show the aggregated values below the total(sample representation as below).Will use the same for Date wise as well as month wise ,year wise slicer. Data Set Sample as below: Also i have created Aggregated Table using Power query which is available as a data set: Also have created measures on values for each measuing point separately which is used for other graphical visuals. Deeply appreciate forums support to meet the objective. Thank you.
Aggregate measure on two field of a visual table
Hi, I'm trying to replicate this table format on PowerBI table visual: I have created Measure1 as follows: Measure1 = CALCULATE(COUNT('Table'[Request Number])) and I put it on G column that is the value of each Field2 in column F in each month. What I want to do is to calculate column E by the same measure grouped by Field1 (column C) for each month. So for example on the first row of column E we have 11 for Field1=X and Month=January, that is 6+4+1 ofthe first three rows Column G, and so on. I tried the GROUPBY or ALLEXCEPT functions by Month and Field1 but it didn't work. How can I build the formula or use particular view for the visual to aggregate the column E by month and Field1?
Table matrix custom representation - using any possible DAX queries
Hi All, I have the below source table as my input which is a table matrix with a set of 5 coulmns namely :- Name ,city, school,Age,type etc. Input source table: Name city school Age type A London ABC 12 Permanent B Berlin ADE 10 Temporary Output expected: Name city A London school ABC Age 12 type Permanent Name city B Berlin school ADE Age 10 type Temporary Is it possible to get the expected output in the given format above .Each row should breakdown into row and column[contianing the row values].Is it possible through any DAX assuming the row headers and column headers wont change anytime in future and no new enteries will be added. Kind regards Sameer
