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
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,
