Create % subtotal of categories filtered by 3rd variable

[lisago1978]

I am trying to create category subtotals on 1 table based on a filter of categories from a 2nd data table.

My first data table 'Inquiries V2'  has a list of caller names (CTName) and I am creating a total of the Entry IDs (unique row ID) based on the total CT name (filtered out 1 CTName category= HCP).

I created a new meaure in this table- 

_GT_I = COUNTX(FILTER('Inquiries V2','Inquiries V2'[Not_HCP]=1),[Entry ID])

Then, I tried to create a % subtotal to a 2nd data table'Inquiries Call Nature' that is long (meaning the Entry IDs are listed multiple times) The relationship between the 2 tables is a many to one baesed on the Entry ID (recall that the Entry ID is unique on the Inquiries V2 table)

I created another measure to do % Call Nature based on the values in the 2nd table and the denominator is the distinct count of Entry IDs from the 1st table

% C_Nature = CALCULATE(DIVIDE(SUM('Inquiries Call Nature'[Value]),'Inquiries V2'[_GT_I],ALLSELECTED('Inquiries Call Nature'[Value]))

I am getting error messages when I try to put this into a table

please advise, I am happy to send you my tables as excel docs or if there is a way to save somewhere in Power BI.

This is a sample of the Nature of Call Table 

date_created	Entry ID	Date/Time of Call	Attribute	Value
6/30/2020 0:00	231143808	4/28/2020 9:59	Questions Guidance	Asking Questions/Seeking Guidance
6/30/2020 0:00	231143808	4/28/2020 9:59	Recommendations/Suggestions	Recommendations/Suggestions
7/22/2020 0:00	239917823	7/22/2020 14:26	Questions Guidance	Asking Questions/Seeking Guidance
7/22/2020 0:00	239917823	7/22/2020 14:26	Report Case/Conact	Report Case/ Inquire Case or Contact Investigation
6/30/2020 0:00	231143824	4/28/2020 8:34	Questions Guidance	Asking Questions/Seeking Guidance
6/30/2020 0:00	231144273	4/1/2020 14:26	Reporting Violation	Reporting Violation to Public Health Orders/Compliance with Order
6/30/2020 0:00	231143618	4/27/2020 11:46	Questions Guidance	Asking Questions/Seeking Guidance
6/30/2020 0:00	231144173	4/23/2020 14:52	Questions Guidance	Asking Questions/Seeking Guidance
6/30/2020 0:00	231144173	4/23/2020 14:52	Recommendations/Suggestions	Recommendations/Suggestions
6/30/2020 0:00	231143900	4/26/2020 10:49	Questions Guidance	Asking Questions/Seeking Guidance
6/30/2020 0:00	231143900	4/26/2020 10:49	Recommendations/Suggestions	Recommendations/Suggestions
6/30/2020 0:00	231143661	4/29/2020 14:42	Questions Guidance	Asking Questions/Seeking Guidance
7/22/2020 0:00	239903620	7/22/2020 13:53	Questions Guidance	Asking Questions/Seeking Guidance
6/30/2020 0:00	231143867	4/27/2020 13:51	Questions Guidance	Asking Questions/Seeking Guidance
7/22/2020 0:00	239896801	7/22/2020 13:40	Questions Guidance	Asking Questions/Seeking Guidance
6/30/2020 0:00	231143644	4/29/2020 15:32	Questions Guidance	Asking Questions/Seeking Guidance
6/30/2020 0:00	231143644	4/29/2020 15:32	General Complaint	General Complaint

 This is a sample of rows for the Inquiries V2 table

date_created	Entry ID	Date/Time of Call	CT Name
6/30/2020	2.31E+08	4/28/2020	General Public
7/22/2020	2.4E+08	7/22/2020	Business
6/30/2020	2.31E+08	4/27/2020	Missing
6/30/2020	2.31E+08	4/28/2020	General Public
6/30/2020	2.31E+08	4/1/2020	Business
6/30/2020	2.31E+08	4/27/2020	Missing
6/30/2020	2.31E+08	4/27/2020	General Public
7/22/2020	2.4E+08	7/22/2020	Health Care Provider
6/30/2020	2.31E+08	4/23/2020	Health Care Provider
6/30/2020	2.31E+08	4/23/2020	General Public
6/30/2020	2.31E+08	4/26/2020	General Public
6/30/2020	2.31E+08	4/29/2020	Business
7/22/2020	2.4E+08	7/22/2020	Special Setting
6/30/2020	2.31E+08	4/27/2020	Business
7/22/2020	2.4E+08	7/22/2020	Special Setting
6/30/2020	2.31E+08	4/27/2020	Health Care Provider
7/22/2020	2.4E+08	7/22/2020	Health Care Provider

[v-xicai]

Hi   @lisago1978 ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[amitchandak]

@lisago1978 , refer 1 of the 3

% C_Nature =DIVIDE(SUM('Inquiries Call Nature'[Value]),calculate(SUM('Inquiries Call Nature'[Value]),all('Inquiries Call Nature')))

% C_Nature =DIVIDE(SUM('Inquiries Call Nature'[Value]),calculate(SUM('Inquiries Call Nature'[Value]),all('Inquiries Call Nature'[_GT_I])))

% C_Nature =DIVIDE(SUM('Inquiries Call Nature'[Value]),calculate(SUM('Inquiries Call Nature'[Value]),allexcept('Inquiries Call Nature'[_GT_I])))

Also refer :

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[lisago1978]

Sorry none of those are working, I changed Entry ID to a whole number, but when I do this formula, which is still not right because it is not filtered on a third value I get 0 or 1 instead of the %

_% Call_N = DIVIDE(SUM('Inquiries Call Nature'[Entry ID]),CALCULATE(SUM('Inquiries Call Nature'[Entry ID]), ALLSELECTED('Inquiries Call Nature'[Value])))

The Value is a set of text categories so at the very least I need subtotals by these categories. I also need the denominator to be a total count minus one category from a different table (variable).

[sturlaws]

Hi @lisago1978 

could you provide a .pbix-file with your data, the measures you have made so far and visuals you need? Upload them to dropbox/onedrive/other and share the link.

Cheers,
Sturla

[v-xicai]

Hi @lisago1978 ,

You may change the Cross filter direction of relationships between the two tables from Single to Both , which will take the two tables treated as a single table, then create measure like DAX below.

_% Call_N = DIVIDE(SUM('Inquiries Call Nature'[Entry ID]),CALCULATE(SUM('Inquiries Call Nature'[Entry ID]), ALLSELECTED('Inquiries Call Nature' )))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lisago1978]

Unfortunately, none of these worked. Could it be the call nature table I created was a subset of the first where I took a wide column and made it long.