cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## How to get the rate per 1,000

Hello

I'm trying to calculate per 1k rate (customer).Here is my dax:

Case rate per 1k Region = DIVIDE('table'[No of Events], MAX('table'[Customers]), 0)*1000

The formula calculates for each Region, but I can't show the All region per 1 k customers (in red). I add the screen shot and the actual table below. Any suggests to show the correct all region raate would be appreciated.

Thank you

 Region Customers No of Events rate per 1k Customers rate per 1k SPLY Higher Level Region Region 1 7,258 913 125.79 105 East Region 2 7,896 836 105.88 91 North Region 3 7,901 1,436 181.75 180 North Region 4 7,136 767 107.48 91 South Region 5 7,318 272 37.17 35 South Region 6 5,817 300 51.57 51 North Region 7 7,900 543 68.73 76 Central Region 8 7,430 332 44.68 37 Central Region 9 8,038 1,575 195.94 213 South Region 10 6,910 372 53.84 59 South Region 11 5,716 927 162.18 128 East Region 12 7,280 708 97.25 98 North Region 13 6,672 601 90.08 73 Central Region 14 5,171 146 28.23 30 South Region 15 7,543 774 102.61 92 Central Region 16 8,144 273 33.52 29 Central Region 17 8,097 339 41.87 53 North Region 18 10,165 947 93.16 89 South Region 19 8,042 550 68.39 70 Central Region 20 7,926 355 44.79 45 North Region 21 8,283 396 47.81 78 North Region 22 9,659 1,136 117.61 112 North Region 23 7,095 934 131.64 143 South Region 24 7,271 1,059 145.65 139 South Region 25 10,181 667 65.51 80 North Region 26 6,634 783 118.03 116 South Region 27 7,312 965 131.97 139 South Region 28 9,582 986 102.90 83 North Region 29 9,461 760 80.33 76 South Region 30 9,682 1,013 104.63 98 South Region 31 8,639 921 106.61 93 East Region 32 8,500 994 116.94 151 East Region 33 9,296 641 68.95 61 North Region 34 14,693 1,130 76.91 74 Central Region 35 5,780 928 160.55 165 East Region 36 8,533 1,731 202.86 189 East Region 37 6,383 594 93.06 109 Central Region 38 7,206 659 91.45 85 South Region 39 6,453 365 56.56 54 Central Region 40 8,104 798 98.47 85 Central Region 41 12,790 731 57.15 66 South Region 42 7,270 825 113.48 114 South Region 43 5,804 1,473 253.79 230 East Region 44 7,497 1,094 145.93 117 East Region 45 6,022 418 69.41 72 North Region 46 15,982 766 47.93 46 Central Region 47 6,692 540 80.69 88 North Region 48 11,220 870 77.54 75 North Region 49 10,083 346 34.32 35 North Region 50 8,633 368 42.63 45 South Region 51 8,001 711 88.86 91 East Region 52 5,715 850 148.73 173 South Region 53 5,483 377 68.76 85 South Region 54 6,375 848 133.02 102 East Region 55 7,245 763 105.31 122 North Region 56 8,548 719 84.11 87 North Region 57 8,912 512 57.45 55 East Region 58 9,134 804 88.02 104 Central Region 59 10,427 1,658 159.01 172 South Region 60 6,906 1,073 155.37 168 East Region 61 6,871 343 49.92 43 South Region 62 7,559 1,424 188.38 159 South Region 63 9,170 1,015 110.69 121 South Region 64 7,980 273 34.21 43 South Region 65 8,397 1,272 151.48 125 North Region 66 7,326 1,085 148.10 135 East All region 533,149 51584 3227.63 3187
5 REPLIES 5
Anonymous
Not applicable

Hello @Anonymous,

Why did you put the "MAX('table'[Customers])"? Because doing that it will always take the highest costumer number, in this case, 15982...

But i think that i didn't understood very well... the value of region 66 and the region 65 on the first screenshot is right, it's not taking the highest value as the DAX, it does not match on the pbi results, right?

Is this "All Region" on the table?

Anonymous
Not applicable

Thanks @Anonymous yes this "All Region" is on the table?.

I think I need some kind of fileter context? I don't know how I to do it?

The red numbers are not showing correctly, after I applied a filter. The filter picks up the total sum of events (7,530) and divide by the highest Region 46's value (15,982). Then it gives 477.16 as the result. This is not what I want.  I want to be able to divide the whole customer number which is 106,480. The answer should be around 71 in stead of 477. Any help would be appriciated.

Thank you

Anonymous
Not applicable

This is the visualisation which shows incorrect number for Central!!

Anonymous
Not applicable

If I apply a filter to show Region 34, the visual shows correct number.  I think I need a differnt and more sophisticated DAX than

Case rate per 1k Region = DIVIDE('table'[No of Events], MAX('table'[Customers]), 0)*1000

Anonymous
Not applicable

Hello!!

Why don't you take this "max" off, and go to excel, do the "sum" and then import do PBI.

Unless if you want for other regions to always divide by the max customer, 15982.. is that the case?

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors