Calculated Table Dax Code using Rollup needs Subtotal labels and sorting
Dear Experts: I would like to create a calculated table with subtotals by country, as depicted in this graphic. That is ... - the Country Subtotals should say 'Subtotal' in the 'Category'-Column and - the Country Subtotals should appear at the bottom row for each country. - The Overall Total Row should say 'Total' in the Category Column Now the Subtotals per Country appear randomly somewhere within the Country related rows and they do not say 'Subtotal' as a Label. Moreover the 'Total Sales' Column should be sorted descending within the respective Country rows. Currently there is no sorting at all for the Total Sales Values. Is this possible by tweaking the below code? That would be fantastic. Help is very much appreciated. Thank you very much in advance. Regards, Andreas The Calculated Dax-Code is as follows so far: SalesSummary-simple = SUMMARIZE( 'DataSource', ROLLUP(DimCtry[Country],DimCat[Category]), "Total Sales", [Total Sales] )Retrieve Top5 Countries Total from a table visual and have it displayed in a card visual?
Dear Experts: I have got a table visual as follows: It shows the Top 5 Countries by Category Sales. along with a Ranking Column. It works just fine. The two measures Top5Countries and Ranking are as follows (please see below). They work just fine everything is ok. Now here comes my question: How can I retrieve The Total Sales as depicted in the Table Visual (142,171,598 €) and have that value displayed in a card visual. Help is very much appreciated. Thank you very much in advance. Regards, Andreas Top5Countries = Var CountryRank = RANKX( FILTER( ALLSELECTED(DimCtry), CALCULATE( [Total Sales], DimProd[CategoryID] = SELECTEDVALUE(DimCat[CategoryID]) // Ensure ranking respects category context ) ), CALCULATE([Total Sales]), , DESC, DENSE ) RETURN IF (CountryRank <= 5, [Total Sales], BLANK()) Ranking = VAR RankValue = RANKX( FILTER( ALLSELECTED(DimCtry), CALCULATE( [Total Sales], DimProd[CategoryID] IN VALUES(DimCat[CategoryID]) ) ), [Total Sales], , DESC, DENSE ) RETURN IF(RankValue <= 5, RankValue, BLANK()) // Only keep ranks 1-5, ignore othersTwo decimal places for values below 5K in a bar chart
Dear Experts: the following measure displays values smaller than 5.000 (5K) with two decimal places, e.g., 3,24K or 0,75K if I use the measure in a table visual. In Germany the thousand separator is the 'period' and the comma is the decimal separator. The trouble is that I cannot use this measure in a bar chart since the FORMAT function converts the numeric result into a text string. Bar charts in Power BI require numeric values for the Y-axis, which is why My TotalSales-Measure could only be added to the Tooltips field bucket and not the Values field bucket. TotalSales = IF( (Sum('DataSource'[Umsatz]))/1000 < 5, Format((Sum('DataSource'[Umsatz])/1000),"0.00K", "de-DE"), Format((Sum('DataSource'[Umsatz])/1000),"#,#00K", "de-DE") ) So, here comes my question? Am I getting this right, I CAN NOT use a custom format in a bar chart so that the bar chart looks like this after having applied a custom format. Help is very much appreciated. Thank you very much in advance. Regards, Andreas
Refresh problem for a large size table in Power BI Desktop and in Power BI Services
I have a table - [WorkItems] - 245 000 records, 90 MB original size - which is part of PBI Report. It contains 30 API references (pulling data directly from API - 30 OData Feeds (Projects)). Code written in PowerQuery (OData) When I am refreshing this PBI report, the [WorkItems] table keeps failing with the following msg - (The Refresh time is over 10 min) I was able to lower [WorkItems] tbl size by removing about half of the API calls (OData feeds) - left 12 out of 30. Then the tbl size reduced to 42MB, and it's now about 100 000 records and refresh is 2 min (3 min when I publish it on PBI Services; scheduled refresh is now not failing). But - my problem - I need to keep ALL 30 OData Feeds (Projects)... To achieve this: - Are there working and more or less quick ways to reduce this tbl size? - Should I add some sort of parameter(s) into Data Source settings - in order to speed up the refresh (to load my records in parts, quicker)? - One other possible option is to add [Date] filter to reduce a tbl size - [Current Date] - 24 months I am allowed to do this. (Not sure where in the code and how exactly)? Here is the code (PowerQuery, OData, I included only 3 out of 30 OData feeds): Any suggestions would be very helpful // "$select=ParentWorkItemId, StoryPoints, State, WorkItemType, Title, IterationSK, AreaSK, WorkItemId" & "&$filter=(WorkItemType eq 'Bug' or WorkItemType eq 'User Story')", null, [Implementation="2.0"]), let Source = OData.Feed("https://analytics.dev.azure.com/MyCompany/Research and Development/_odata/v3.0-preview/WorkItems?" & "$select=ParentWorkItemId, StoryPoints, State, WorkItemType, Title, IterationSK, AreaSK, WorkItemId, Area" & "&$filter=(WorkItemType eq 'Bug' or WorkItemType eq 'User Story')" & "&$expand=Area($select=AreaPath)", null, [Implementation="2.0"]), #"Add all Ops & CP projects" = Table.Combine({ Source, OData.Feed("https://analytics.dev.azure.com/MyCompany/Cloud Platform/_odata/v3.0-preview/WorkItems?" & "$select=ParentWorkItemId, StoryPoints, State, WorkItemType, Title, IterationSK, AreaSK, WorkItemId, Area" & "&$filter= (WorkItemType eq 'Bug' or WorkItemType eq 'User Story')" & "&$expand=Area($select=AreaPath)", null, [Implementation="2.0"]), OData.Feed("https://analytics.dev.azure.com/MyCompany/Batch Management/_odata/v3.0-preview/WorkItems?" & "$select=ParentWorkItemId, StoryPoints, State, WorkItemType, Title, IterationSK, AreaSK, WorkItemId, Area" & "&$filter= (WorkItemType eq 'Bug' or WorkItemType eq 'User Story')" & "&$expand=Area($select=AreaPath)", null, [Implementation="2.0"]), }), #"Add AreaPath" = Table.ExpandRecordColumn(#"Add all Ops & CP projects", "Area", {"AreaPath"}, {"AreaPath"}), // Calculate the date 24 months ago from the current date Date24MonthsAgo = Date.AddMonths(DateTime.LocalNow(), -24), // Filter data to include only records from the last 24 months FilteredData = Table.SelectRows(ConvertedIDColumns, each DateTime.From([CreatedDate]) >= Date24MonthsAgo), #"Rename Story Points to Effort" = Table.RenameColumns(#"Add AreaPath",{{"StoryPoints", "Effort"}}), #"Add Organization" = Table.AddColumn(#"Rename Story Points to Effort", "Organization", each "MyCompany"), #"Change IDs to text" = Table.TransformColumnTypes(#"Add Organization",{{"WorkItemId", type text}, {"ParentWorkItemId", type text}}), #"Make IDs unique" = Table.TransformColumns( #"Change IDs to text", { { "WorkItemId", each Text.Combine({(_),"-VSTS"}), type text } } ), #"Make Parent IDs unique" = Table.TransformColumns( #"Make IDs unique", { { "ParentWorkItemId", each Text.Combine({(_),"-VSTS"}), type text } } ), #"Replaced Value" = Table.ReplaceValue(#"Make Parent IDs unique","-VSTS","",Replacer.ReplaceValue,{"ParentWorkItemId"}), #"Parent Orphans to ""No Feature""" = Table.ReplaceValue(#"Replaced Value","","No Feature",Replacer.ReplaceValue,{"ParentWorkItemId"}) in #"Parent Orphans to ""No Feature"""
DAX formula to calculate difference between current Q and previous Q.
Hi all- I have the below data, and looking to calculate the difference between the current Q from the previous Q. So far, i have the formula: QoQ $ = IF( ISFILTERED('ICC Reports'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_QUARTER = CALCULATE(SUM('ICC Reports'[$]), DATEADD('ICC Reports'[Date].[Date], -1, QUARTER)) RETURN DIVIDE(SUM('ICC Reports'[$]) - __PREV_QUARTER, __PREV_QUARTER) ) Thank youSum and Percentage Calculation of current 3 months
Hello there - I hope to get help as i am having a little trouble with my DAX calculation. I want to calculate Hedge Ratio and Dollar Mismatch of the current 3 months, 6 months, 12 and 24. The result i want is to look like below: To calculate Hedge Ration = Sum of the Current 3months of Column G divided by the Sum of the Current 3 months of Column H. While to Calculate Dollar Mismatch= Sum of the Current 3months of Column J Thank you.I need Help for my DAX. If the Cycle time is during The MCO how to calculate the days During MCO
Hi All, I have 1 impediment to calculate the Days During MCO on my queries. This is to calculate how many days are there by using start date MCO and End date MCO. Below is the example of my queries. For your reference below is the output that i wanted including the start date and the end date of the MCO i include how i do calculate the Cycle time . How can i use queries or using measure to auto populate the Days During MCO for my Data. Hope you all can help me. Best regards Thanks Source Table Item Incurred Resolved Cycle Time Days During MCO Cycle Time Exclude MCO A 8/29/2021 9/14/2021 17 B 8/28/2021 9/14/2021 18 C 8/26/2021 9/14/2021 20 D 8/25/2021 10/10/2021 47 E 8/25/2021 10/10/2021 47 F 8/24/2021 10/10/2021 48 G 8/23/2021 9/14/2021 23 H 8/23/2021 11/30/2021 99 I 8/20/2021 11/16/2021 89 J 8/20/2021 9/14/2021 26 K 8/19/2021 9/14/2021 27 L 8/18/2021 9/14/2021 28 M 8/18/2021 9/14/2021 28 N 8/16/2021 9/14/2021 30how to write a DAX to change a number to two names
Hi everyone I have a problem changing chart values and writing a DAX . General Details: I have a curtain report showing values as 4W (4 in powerbi) on different times. I want to make the report more menaiful be showing the values as " open " or "close" first value is always open , second value is close. as the expested chart below I am thinking of something like this If first value ( curtian value=4 ), showing the behavior as " open". Any ideas? Many Thanks,
