Dax to calculate percent of column total with Category Rows and Month Columns

[vrajkumar1]

Hello:

Here is how my data looks - CurrentCharges for each category for each month

Prod3_Grouped	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
C	76,556,776	69,852,012	76,039,508	71,613,839	75,774,776	78,808,864	77,248,516	86,241,482	74,587,233	83,202,627	83,935,886	98,487,949
Mc	834,695,962	762,602,711	866,421,881	773,424,365	840,332,890	840,174,909	763,961,792	855,388,635	793,547,974	857,156,366	827,523,867	810,000,690
Md	666,885,243	623,861,038	699,543,828	631,394,544	674,461,627	662,825,381	635,040,815	662,588,453	623,916,697	637,112,313	628,932,253	608,014,145
MdC	827,897,970	803,486,757	910,346,635	819,489,342	876,323,226	875,717,984	835,123,633	912,479,858	841,860,597	925,525,237	873,932,836	904,604,463
MdCr	989,628,595	945,478,482	1,091,081,519	966,167,892	1,072,312,347	1,048,531,013	969,583,869	1,069,302,461	1,024,134,346	1,072,733,314	1,046,526,260	1,022,601,712
Other	93,316,518	91,728,977	106,519,154	94,614,006	96,372,026	100,507,224	97,046,562	105,303,676	96,023,236	101,188,840	102,321,164	98,565,521
Total	3,488,981,064	3,297,009,976	3,749,952,526	3,356,703,989	3,635,576,893	3,606,565,374	3,378,005,186	3,691,304,566	3,454,070,083	3,676,918,698	3,563,172,267	3,542,274,481

This is what I am expecting to see - Current charges for each category as percent of column total for each month 

Prod3_Grouped	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
C	2.2%	2.1%	2.0%	2.1%	2.1%	2.2%	2.3%	2.3%	2.2%	2.3%	2.4%	2.8%
Mc	23.9%	23.1%	23.1%	23.0%	23.1%	23.3%	22.6%	23.2%	23.0%	23.3%	23.2%	22.9%
Md	19.1%	18.9%	18.7%	18.8%	18.6%	18.4%	18.8%	17.9%	18.1%	17.3%	17.7%	17.2%
MdC	23.7%	24.4%	24.3%	24.4%	24.1%	24.3%	24.7%	24.7%	24.4%	25.2%	24.5%	25.5%
MdCr	28.4%	28.7%	29.1%	28.8%	29.5%	29.1%	28.7%	29.0%	29.7%	29.2%	29.4%	28.9%
Other	2.7%	2.8%	2.8%	2.8%	2.7%	2.8%	2.9%	2.9%	2.8%	2.8%	2.9%	2.8%
Total	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%

I would like use a dax measure and not the built-in 'Show Value as...;

Here is what I have tried

CurrCharges_Pct =

Var _TotalCharges =

CALCULATE(SUM(MVI_PayorMixShift[CURR_CHGS]), ALL(MVI_PayorMixShift[Prod3_Grouped]))

RETURN

DIVIDE (SUM(MVI_PayorMixShift[CURR_CHGS]), _TotalCharges)

Using Feb as an example.

I need to get 

CALCULATE(SUM(MVI_PayorMixShift[CURR_CHGS]), ALL(MVI_PayorMixShift[Prod3_Grouped])) = 3,297,009,976

So that each category charge is divided by this total number - and I am not able to get to this.

If I use 

CALCULATE(SUM(MVI_PayorMixShift[CURR_CHGS]), ALL(MVI_PayorMixShift))  I get 42,440,535,104 - which is not what I want.

How do I get the total excluding category breakdown but including per month

It seems just a simple fix - but I am not able to get it.

Can someone help please.

Thank you.

VR.

[vrajkumar1]

Payor Product	Sum_Current
C	69852012
O	91728977
Md	623861038
Mc	762602711
MdC	803486757
MdCr	945478482

Trying one more time to send the data.  

[lbendlin]

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[vrajkumar1]

Yes. I followed the instructions provided here.

[vrajkumar1]

But this is the basic data

C	69852011.53
Mc	762602710.55
Md	623861037.80
MdC	803486756.77
MdCr	945478481.91
Other	91728977.19

[vrajkumar1]

I have read the post on how to upload data

But I keep getting invalid HTML.

[vrajkumar1]

Prod3Grouped	CURR_CHGS	Excel	PBI
C	69,852,012	2.1%	100%
Mc	762,602,711	23.1%	100%
Md	623,861,038	18.9%	100%
MdC	803,486,757	24.4%	100%
MdCr	945,478,482	28.7%	100%
Other	91,728,977	2.8%	100%

[vrajkumar1]

for the first row I am not getting 2.1% - 202% = -.1%  Instead I get 3.49%

[lbendlin]

My proposal shows 

That looks accurate to me? What would you expect instead? - 0.07% ?

[vrajkumar1]

Yes.  I am expecting -0.07% which is 2.19% - 2.12%

[lbendlin]

MoM = 
var m = max(MVI_PayorMixShift[Month])
var p = CALCULATE([% Col Tot],MVI_PayorMixShift[Month]=EDATE(m,-1))
return [% Col Tot]-p

[vrajkumar1]

Thank you.

Is % Col Tot what you suggested before:

DIVIDE(SUM(MVI_PayorMixShift[Value]), CALCULATE(SUM(MVI_PayorMixShift[Value]), REMOVEFILTERS(MVI_PayorMixShift[Prod3_Grouped])),0)
Can I add % Col Tot as a Variable in this same formula ?

[lbendlin]

yes, and yes.

[vrajkumar1]

Thank you.  I tried it and getting the following error

"Calculation error in measure 'MVI_PayorMixShift'[MOM %]: DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

MM is a varchar datatype.

[lbendlin]

where is MM coming from? I don't see that anywhere in your data.

[vrajkumar1]

MM is the Month variable. 

So var m = max(MVI_PayorMixShift[MM]) which is what is each column below.

I think I got the data in correctly (I think)

Prod3Grouped	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept
C	76,556,776	69,852,012	76,039,508	71,613,839	75,774,776	78,808,864	77,248,516	86,241,482	74,587,233
Mc	834,695,962	762,602,711	866,421,881	773,424,365	840,332,890	840,174,909	763,961,792	855,388,635	793,547,974
Md	666,885,243	623,861,038	699,543,828	631,394,544	674,461,627	662,825,381	635,040,815	662,588,453	623,916,697
MdC	827,897,970	803,486,757	910,346,635	819,489,342	876,323,226	875,717,984	835,123,633	912,479,858	841,860,597
MdCr	989,628,595	945,478,482	1,091,081,519	966,167,892	1,072,312,347	1,048,531,013	969,583,869	1,069,302,461	1,024,134,346
Other	93,316,518	91,728,977	106,519,154	94,614,006	96,372,026	100,507,224	97,046,562	105,303,676	96,023,236
Total	3,488,981,064	3,297,009,976	3,749,952,526	3,356,703,989	3,635,576,893	3,606,565,374	3,378,005,186	3,691,304,566	3,454,070,083

[lbendlin]

You cannot do time math with month names. As you see from my suggested version you need to use dates or datetimes.

[vrajkumar1 · Accepted solution]

Agree.  Thats why I went the route of calculating Percent of Column Total which is where I am having an issue with the formula:

DIVIDE(SUM(MVI_PayorMixShift[Value]), CALCULATE(SUM(MVI_PayorMixShift[Value]), REMOVEFILTERS(MVI_PayorMixShift[Prod3_Grouped])),0) for data like below

C	69852011.53
Mc	762602710.55
Md	623861037.80
MdC	803486756.77
MdCr	945478481.91
Other	91728977.19

[lbendlin]

I don't see a problem. The explicit measure matches the implicit measure.

[vrajkumar1]

Thank you very much for your help with all this.

The reason I was getting 100% and not what you showed was because - the column I used in REMOVEFILTERS (which is the row breakdown) - this column was sorted (in the DATA pane) by another SORTORDER column.  Once I removed that, then the percentages were accurate.  

[vrajkumar1]

I have added the data below as per instructions in the forum.

Here is one set of data - current charges.  

Prod3Grouped	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
C	76,556,776	69,852,012	76,039,508	71,613,839	75,774,776	78,808,864	77,248,516	86,241,482	74,587,233	83,202,627	83,935,886	98,487,949
Mc	834,695,962	762,602,711	866,421,881	773,424,365	840,332,890	840,174,909	763,961,792	855,388,635	793,547,974	857,156,366	827,523,867	810,000,690
Md	666,885,243	623,861,038	699,543,828	631,394,544	674,461,627	662,825,381	635,040,815	662,588,453	623,916,697	637,112,313	628,932,253	608,014,145
MdC	827,897,970	803,486,757	910,346,635	819,489,342	876,323,226	875,717,984	835,123,633	912,479,858	841,860,597	925,525,237	873,932,836	904,604,463
MdCr	989,628,595	945,478,482	1,091,081,519	966,167,892	1,072,312,347	1,048,531,013	969,583,869	1,069,302,461	1,024,134,346	1,072,733,314	1,046,526,260	1,022,601,712
Other	93,316,518	91,728,977	106,519,154	94,614,006	96,372,026	100,507,224	97,046,562	105,303,676	96,023,236	101,188,840	102,321,164	98,565,521
Total	3,488,981,064	3,297,009,976	3,749,952,526	3,356,703,989	3,635,576,893	3,606,565,374	3,378,005,186	3,691,304,566	3,454,070,083	3,676,918,698	3,563,172,267	3,542,274,481

and below is what I want as percent of column total

Prod3Grouped	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
C	2.2%	2.1%	2.0%	2.1%	2.1%	2.2%	2.3%	2.3%	2.2%	2.3%	2.4%	2.8%
Mc	23.9%	23.1%	23.1%	23.0%	23.1%	23.3%	22.6%	23.2%	23.0%	23.3%	23.2%	22.9%
Md	19.1%	18.9%	18.7%	18.8%	18.6%	18.4%	18.8%	17.9%	18.1%	17.3%	17.7%	17.2%
MdC	23.7%	24.4%	24.3%	24.4%	24.1%	24.3%	24.7%	24.7%	24.4%	25.2%	24.5%	25.5%
MdCr	28.4%	28.7%	29.1%	28.8%	29.5%	29.1%	28.7%	29.0%	29.7%	29.2%	29.4%	28.9%
Other	2.7%	2.8%	2.8%	2.8%	2.7%	2.8%	2.9%	2.9%	2.8%	2.8%	2.9%	2.8%

Here is second set of data - previous charges.  and below that is what I want as percent of column total

Prod3Grouped	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
C	-	76,556,776	69,852,012	76,039,508	71,613,839	75,774,776	78,808,864	77,248,516	86,241,482	74,587,233	83,202,627	83,935,886
Mc	-	834,695,962	762,602,711	866,421,881	773,424,365	840,332,890	840,174,909	763,961,792	855,388,635	793,547,974	857,156,366	827,523,867
Md	-	666,885,243	623,861,038	699,543,828	631,394,544	674,461,627	662,825,381	635,040,815	662,588,453	623,916,697	637,112,313	628,932,253
MdC	-	827,897,970	803,486,757	910,346,635	819,489,342	876,323,226	875,717,984	835,123,633	912,479,858	841,860,597	925,525,237	873,932,836
MdCr	-	989,628,595	945,478,482	1,091,081,519	966,167,892	1,072,312,347	1,048,531,013	969,583,869	1,069,302,461	1,024,134,346	1,072,733,314	1,046,526,260
Other	-	93,316,518	91,728,977	106,519,154	94,614,006	96,372,026	100,507,224	97,046,562	105,303,676	96,023,236	101,188,840	102,321,164
Prod3Grouped	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
C	-	2.2%	2.1%	2.0%	2.1%	2.1%	2.2%	2.3%	2.3%	2.2%	2.3%	2.4%
Mc	-	23.9%	23.1%	23.1%	23.0%	23.1%	23.3%	22.6%	23.2%	23.0%	23.3%	23.2%
Md	-	19.1%	18.9%	18.7%	18.8%	18.6%	18.4%	18.8%	17.9%	18.1%	17.3%	17.7%
MdC	-	23.7%	24.4%	24.3%	24.4%	24.1%	24.3%	24.7%	24.7%	24.4%	25.2%	24.5%
MdCr	-	28.4%	28.7%	29.1%	28.8%	29.5%	29.1%	28.7%	29.0%	29.7%	29.2%	29.4%
Other	-	2.7%	2.8%	2.8%	2.8%	2.7%	2.8%	2.9%	2.9%	2.8%	2.8%	2.9%