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!Display field value on rows and count in the total
Hi, I have a unique requirement, where I need to display textual content applicable in the table rows, while displaying the distinct count of the same in total. Each field displayed in the table may be filtered. Is it possible to create a DAX expression/ is there a work around to meet this requirement? For example, in the snapshot below, I m displaying distinct count of Column B elements in the total. However, the expression fails when there is a single element as it was not possible to distingish between a row item and total DAX I used: ColB or Distinct Count = var _x = DISTINCTCOUNT(Test[ColB]) var _y = COUNTROWS(Test) RETURN IF(_y=1, IF(_x=1 , SELECTEDVALUE(Test[ColB]), "1"), _x & "") Sample data let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7BCQAxCAR78Z1H1NjAWYak/zaSjSTgfQYZZsEIYmr0AQ5wp9mCBBJw4CgdcHrDdEVJ/8m3PVPA4TU7g7TSSfllZMfvF1y25Vw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, ColC = _t, ColD = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", Int64.Type}, {"ColB", type text}, {"ColC", type text}, {"ColD", Int64.Type}}) in #"Changed Type" Thanks, Mannu
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
Create a table for the Lastest avaliable Non blank value
Hi there, DAX newb here so thanks in advance 🙂 I'm working on a Project info table that is built based on a two tables relation - a Master table contains the main project info (ID, Description ) - A latest summary table with latest summary submission and corresponding summary by project managers (PMs) the master table is on the one side , so many project summaries can submitted for the one project vs time. Constrain: Some project summaries are blank or contain whitespaces as these are error submissions by PMs. These should not be displayed. My hacky solution is below: 1- _Last_known_summ = LASTNONBLANK(Project_Summary[Management_Summary], Fact_Project_Summary[Management_Summary]) 2- and a visual filter for the last top 1 submitted date_time. The issue that i have is that if two or more projects are selected on the filter ; only the project with the latest summary will be displayed . If i remove the filter stated in 2 , the table index will explode and all non blank summaries will be displayed. help 🙂
Filter a table visual based on the latest data update
Hello guys! I have a dashboard which it's data is based on a position for open items, so every day I have a programmed routine that downloads the data into my dashboard and it get's updated. Considering the data is for open items, one specific item can appear multiple times until it's cleared, so I've been working only with the latest date and keep the past information as history. Here's a glimpse of the dashboard: On the top right, I created a measure that gets the latest date on the data and all the other measures also use that information to display it's info (based on the context). I also created a line chart so people can see the data history and to be able to select a specific date in time, so when I interact with a data point on the line chart all the data get's updated to that specific position/context in time. Everything works fine, except for one particular visual. As you guys can see, there is a table visual; the highlighted column on the table specifies what database the user is seeing the information and it's not having the same behaviour as the other visuals on the dashboard, because it's displaying all the data when it should only bring data from 2020-03-27; I have a few columns on that table that are actually measures, the same as the ones being used on the other visuals, that get's the maximum date from the context, but they are not filtering the data on the table. When I select a specific date on the line chart, as obviously expected, the table gets filtered alright, as the other visuals, but what I need for it to do is, when nothing is selected, to display only the data from the latest date available. Any thoughts on the matter? I'm pretty sure I can solve this with DAX, but everything I tried works with every single visual available, except for tables. Thank you very much!
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
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.
