%grand total

4 Topics

Percentage Calculation using multiple tables columns -DAX Function
Greetings, I have three table(s) in Semantic layer and have one-to-many with bi-directional relationship between tables and I have shown the sample DAX function the below I have used for my reports. Table-1: has only one record per id Table-2: can have many records and the grain is name and Id Table-3: can have many records and the grain is name, outcome Table-1 Table -2 Bi-directional - One to Many Table-1 Table-3 Bi-directional - One to Many Outcome %= DIVIDE(SUM('Table-2'[Hours]) , CALCULATE( SUM('Table-2'[Hours]), ALLSELECTED('Table-3'[Outcome]) ) ) Category Group %= DIVIDE(SUM('Table-2'[Hours]) , CALCULATE( SUM('Table-2'[Hours]), ALLSELECTED('Table-3'[Category Group]) ) ) Question-1: I have created data group column on category and when I use data group column percentage, its working as expected but if I create the column in Power query using category and use the column in percentage like the below, it’s not working as expected. Any thoughts on this. Category Group %= DIVIDE(SUM('Table-2'[Hours]) , CALCULATE( SUM('Table-2'[Hours]), ALLSELECTED('Table-3'[Category Group]) ) ) Question -2: I have created Donut chart or pie chart or graph, its working for one specific column either category or outcome. If I drill down to Category Group from Outcome or from Category Group to Category, it's not working as expected and showing the values as 100%. I have changed the logic the below this but no luck Category Group %= DIVIDE(SUM('Table-2'[Hours]) , CALCULATE( SUM('Table-2'[Hours]), ALLSELECTED('Table-1'[Category Group]) , 'Table-1'[Category ]) ) ) Is there any way to write a single Dax measure to support all three combinations to create percentage metric. I have shown the sample table the below for your quick reference. Table-2 Name Category Category Grp Outcome Total Hours Grand Total Percentage Outcome Percentage Outcome Grp Percentage Name-1 Category -1 Category -1 Outcome -1 7 19 36.84211 = (7/19)*100 = 7/19 *100 Name-1 Category -2 Category -2 Outcome -2 4 19 21.05263 = (7/19) *100 =4/19*100 Name-1 Category -6 Other Outcome -2 3 19 15.78947 = (7/19) *100 =8/19*100 Name-1 Category-8 Other No Outcome 5 19 26.31579 =(5/19) *100 =8/19*100 Do you have any other recommendations ? Thanks you in advance for your help on this. Table-2 Table-3 Table -1 ID Table 2 Name ID Outcome ID name Category Category Group Hours 1 Name -1 1 Outcome -1 1 name 1 Category -1 Category -1 5 2 Name -1 2 Outcome -1 2 name 2 Category -1 Category -1 2 5 Name -1 5 Outcome -2 3 name 3 Category -1 Category -1 4 8 Name-1 8 Outcome -2 4 name 4 Category -2 Category -2 3 10 Name-1 10 No Outcome 5 name 5 Category -2 Category -2 5 6 name 6 Category -2 Category -2 5 7 name 7 Category -5 Category -2 3 8 name 8 Category -6 Other 2 9 name 9 Category -7 Other 6 10 name 10 Category -8 Other 6
Solved
OrreBI
1 year ago Place DAX Commands and Tips
1.6KViews
0likes
2Comments
Correct Total using ALL() whilst also having blanks filtered out of visual?
Hi I'm hoping someone may have a quick solution to a problem i'm encountering whilst trying to provide % of total in a bar chart. The screenshot below shows a dummy dataset for customer feedback replicating the issue i'm encountering. The data spans over 3 days (20th Oct to 22 Oct), with a total of 25 customers, 9 of which opted not to give feedback. I'm needing to present this data as a % broken out by feedback score. I have used the 'feedback%' measure shown below, which leverages the ALL() function to clear the feedback_rating filter, providing a total customer count. You can see from the 2nd bar chart that this works when the blank feedback rating option is included in the visual. Example feedback score of 1 has 5 customer entries, 5/25 = 20%. I have been asked to remove blanks from the visual. When these are removed however, the % are incorrect as can be seen within the red bar chart, a feedback score of 1 now has a % distribution of 26%. The reason appears to be due to the Date slicer which is required in my real dashboard. As all the customers on 21st October did not leave feedback, I believe when the blanks are removed from the visual, all of these rows are removed from the underlying data, creating a new customer total of 19, resulting in incorrect %'s, feedback score of 1 now becoming 26% (5/19) as mentioned above. Any ideas on how to keep these values in the total whilst also keeping the blank entries out of the visual? Ideally I would like to solve it within a DAX measure as my existing dashboard already has a relatively complex data model, however I appreciate it may require a helper table of some sort. feedback% = distinctcount(Sheet1[customer_id]) / CALCULATE(DISTINCTCOUNT(Sheet1[customer_id]),ALL(Sheet1[feedback_rating])) Thanks for reading.
dtd_89
2 years ago Place DAX Commands and Tips
1.5KViews
0likes
9Comments
Need help with DAX
I need to display No% and Yes%. I have market and sales count. I need to display No% and Yes%. No% =( Sales Count/Grand Total ) * 100 - Ex : (122/370)*100 = 33% Yes% = 100 - No% - Ex : 100-33 = 67% Can you please help me with the DAX to get the No% and Yes%? I have to finally visualize the Yes% in a bar chart.
m__m
3 years ago Place DAX Commands and Tips
706Views
0likes
3Comments
How to calculate the %grand total by day
Hi guys, I am a green hand, just met a problem that I would like to get a help . I don't calculate the %GT sum of reg during whole period like below, I want to calculate by day, for exampler how much of percentage of affiliates share in 1/7 (100%), how should I write the dax ?
Solved
clarafff
3 years ago Place DAX Commands and Tips
895Views
0likes
3Comments