How to calculate percentage of each group out of total number of distinct rows
Hello! I have a table that looking at type of insurances that customers have and customers could select more than one type, however ultimately, I would like to create a visual in my dashboard that shows the percentage of responses for each insurance type OVER the DISTINCT number of respondents. For example, if 301 people selected 'Medicaid' (this includes folks who selected only Medicaid or selected Medicaid and other types of insurance, for the numerator all I care about is that they selected 'Medicaid') out of 861 DISTINCT individuals who responded to the survey, how do I get a percentage that reflects non-distinct count per group OVER a distinct number of IDs? Here is my visual thus far: ^The percentages you see at the bottom of each square in the tree map DO represent what I am looking for, for example 301/861 folks= 34.96% selected Medicare, however I had calculated that in R before exporting to Excel and then importing into Power BI. I want to calculate this however IN Power BI if possible instead because when I try to filter based on age, educational level, etc. as seen on the right-side of the screen, those percentages do not change. Here is my table that the visual is based on- column Q1= ID of individual, column insurance= insurance type, total_insurance= distinct count of Q1 (I calculated this in R) & count_insurance=count of insurance by group /total_insurance, again calculated in R. Any suggestions for how to calculcate total_insurance & count_insurance in PowerBI rather than bringing them over from R would be much appreciated, thank you! 😊 How do I recreate these last two columns exactly as they are but using a calculation made in PowerBI rather than R?^ Attached is a PowerBI workbook at this link in Dropbox https://www.dropbox.com/scl/fi/fmtzurp0k6jkl36bk7qll/insurance_long.pbix?rlkey=i6ph2ji5ll9hwicv14yjtcg5q&st=upw9xiot&dl=0 . The table in my screenshot above is for the insurance_long table in the PowerBI workbook. The sheet_na table in the PowerBI workbook contains the ID "Q1" and the age category variable used as a filter on the far right of the visual "Q4_cat" and then there is also a table for race as that also serves as one of the filters. Thank you!
Distinct Count ignoring one column plus calculate average
Hello, all! I have the following table And im trying to get a simple DISTINCT COUNT[ID] but its not working because the column Type has two different values (B and E). If i use a mesure, it works BUT im trying to use a Column because after i get the distinct count, ill need get the average. Like the example below: In short, i need to get the average of distinct count[id]. Ive tried create a column with CALCULATE(DISTINCTCOUNT(Consult[id]) / DISTINCTCOUNT(Consult[date].[Month]), ALLSELECTED(Consult[date].[Day])) But its not working because its counting the same ID twice when the ID has the B and E type. If there is a way to get the column average that i need using a measure, ill be glad to learn how. Thanks!
Get the latest record in an SCD2 for a unique ID, count unique IDS, within period set by date slicer
Hi all I have been struggling with this challenge latetly. My goal is to create a measure that count unique IDs that has the "Priority" of 1, within a set date period from the date slicer. The data comes from an SCD2 table that records entries of all unique contracts ("Applicants"). The one column that changes frequently is the column named "Priority". My goal is to, within the set date period from the date slicer, find the latest entry for all unique contracts and then do a unique count for all with "Priority" of 1. "Priority" can be filtered on the report page as well so I guess that filtering doesnt need to be done in DAX. The measure uses USERELATIONSHIP with "ApplicationDate". In plains SQL this can be solved with a ROW_NUM() with descending order, and then filter on the row number. I havent managed to use ROWNUMBER() in DAX in a dynamic way, having the date slicer dictating the ROWNUMBER() output and passing it forward in the measure. One way I could think of solving this is to lock in the count for each locked time period (say per week), but the users would like to see if it works with the date slicer. Sample from my table (Sorted by RecordEffectiveDate): MemberIDSource Priority ApplicationDate RecordEffectiveDate IsCurrent 454 1 2023-01-07T16:36:04.000+00:00 2023-01-10T00:00:00.000+00:00 FALSE 454 2 2023-01-07T16:36:04.000+00:00 2023-01-21T00:00:00.000+00:00 FALSE 789 1 2023-01-19T17:16:05.000+00:00 2023-01-21T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-08T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-08T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-09T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-09T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-10T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-16T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-07-19T00:00:00.000+00:00 FALSE 789 3 2023-01-19T17:16:05.000+00:00 2023-07-24T00:00:00.000+00:00 TRUE 1011 1 2023-07-20T13:17:14.000+00:00 2023-07-24T00:00:00.000+00:00 FALSE 454 2 2023-01-07T16:36:04.000+00:00 2023-07-24T00:00:00.000+00:00 FALSE 1011 2 2023-07-20T13:17:14.000+00:00 2023-08-02T00:00:00.000+00:00 TRUE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-02T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-23T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-24T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-24T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-25T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-09-22T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-12-06T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2024-01-13T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2024-01-31T00:00:00.000+00:00 TRUE Any help very much appreciated.
Get the Sum of distinct count
Dear Community Members, I have a dax calculation that gets the count of students that were admitted 4 years ago. My challenge is how to sum it, the result gives me the last value as the total instead of the sum of each row. Please find my DAX query below: Student Count Adm 4Yrs = CALCULATE( DISTINCTCOUNT(Admissions[AUGSTNO]), DATEADD(DATESYTD(DimDate[Date]),-4,YEAR), USERELATIONSHIP(Admissions[AUGSTNO],Graduation[GSTNO]) ) What I expect is the total to be the sum of the Student Count Adm 4yrs for each year. Any direction to resolve this would be appreciated.
COUNTDISTINCT vs COUNT for coverage calculations
Hi All, I am struggling to work out what I think should be some fairly straightforward DAX and can't find anything in the forum. Apologies if I have missed another post. I have a client and product columns. What I am trying to achieve is to calculate what 'client coverage' each product has. Client coverage is defined by the percentage of clients that have a product and is between 0% and 100%. What is throwing a spanner in doing a COUNT(Products)/COUNT DISTINCT(Clients) is that some clients have some products more than once; the way I want my calculation to work is to be binary: A client has it or doesn't. I am not interested in how many times they have bought it. Example data: Client Product A 1 A 2 A 3 B 1 B 1 B 2 C 1 In this instance, all clients have product 1, but Client B has it twice. Therefore coverage should be 100% not 133% if I did the formula above. Expected calculated output: Product Client Coverage 1 100% 2 67% 3 33.33% Any help would be greatly appreciated. Thank you!Count in SCD2 dimension - optimization
Hello PB community! Can anybody please help me with DAX optimization in this scenario: I have Tabular model with 10 dimension tables, no real facts. One of the user request is to count units in Units table through time. This table is a SCD2 table type and only 2 dates in table are valid_from and valid_to. They will use year-month and year attributes on reports and in slicers. Beside date attribute they wan't to use attributes Units Type and Country of Origin. I did't connect my Calendar table with Units table in Tabular model and I have combined this DAX code: Cnt of units:= CALCULATE ( // DISTINCTCOUNT ( 'Units'[unit_id] ), SUMX(VALUES('Units'[unit_id] ),1), FILTER ( 'Units', COUNTROWS ( FILTER ( VALUES ( 'CalendarTable'[Date] ), 'CalendarTable'[Date]>= 'Units'[unit_valid_from] && 'CalendarTable'[Date] <= 'Units'[unit_valid_to] ) ) ) ) So this code is doing some sort of cross join / cross aplly on date table and since Units table isn't really small (it has 500K+ rows) it's slow. Calendar table is from DB Model, not from PB and has data from 2015 to 2025. Current server timing from dax studio are like this: In grid: Year Month, Unit type, Country of origin and my metric Filter: Year in (2020, 2019) I already replaced DISTINCOUNT with SUMX code. With using DISTINCOUNT function: So, are there are ideas how to optimize this code even more? Is there any way I can solve this differently (connections in Tabular with Calendar table, etc..?) Similar problems: https://community.powerbi.com/t5/Desktop/How-to-find-number-of-customers-in-a-SCD2-type-table/m-p/97281Calculate the growth in unique objects by month
Hi, I've been looking around and have found lots of examples of calculating the number of unique values occuring per year or per month, for example for calculating the number of unique website visitors per month. But what I need to do it calculate the number of truly new unique values each month, so it's a calculation of growth not occurance. For example: MonthYear Company Name Feb-20 Company A Feb-20 Company B Mar-20 Company A Mar-20 Company C Would be displayed as: MonthYear Growth Feb-20 2 Mar-20 1 Any help?
