Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Using Feb as an example.
I need to get
Solved! Go to 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 |
Payor Product | Sum_Current |
C | 69852012 |
O | 91728977 |
Md | 623861038 |
Mc | 762602711 |
MdC | 803486757 |
MdCr | 945478482 |
Trying one more time to send the data.
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-...
Yes. I followed the instructions provided here.
But this is the basic data
C | 69852011.53 |
Mc | 762602710.55 |
Md | 623861037.80 |
MdC | 803486756.77 |
MdCr | 945478481.91 |
Other | 91728977.19 |
I have read the post on how to upload data
But I keep getting invalid HTML.
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% |
for the first row I am not getting 2.1% - 202% = -.1% Instead I get 3.49%
My proposal shows
That looks accurate to me? What would you expect instead? - 0.07% ?
Yes. I am expecting -0.07% which is 2.19% - 2.12%
MoM =
var m = max(MVI_PayorMixShift[Month])
var p = CALCULATE([% Col Tot],MVI_PayorMixShift[Month]=EDATE(m,-1))
return [% Col Tot]-p
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 ?
yes, and yes.
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.
where is MM coming from? I don't see that anywhere in your data.
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 |
You cannot do time math with month names. As you see from my suggested version you need to use dates or datetimes.
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 |
I don't see a problem. The explicit measure matches the implicit measure.
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.
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% |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |