Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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

 

Toshie_0-1670349847011.png

 

Region Customers No of Eventsrate per 1k Customers rate per 1k SPLYHigher Level Region
Region 1          7,258                                  913125.79105East
Region 2          7,896                                  836105.8891North
Region 3          7,901                               1,436181.75180North
Region 4          7,136                                  767107.4891South
Region 5          7,318                                  27237.1735South
Region 6          5,817                                  30051.5751North
Region 7          7,900                                  54368.7376Central
Region 8          7,430                                  33244.6837Central
Region 9          8,038                               1,575195.94213South
Region 10          6,910                                  37253.8459South
Region 11          5,716                                  927162.18128East
Region 12          7,280                                  70897.2598North
Region 13          6,672                                  60190.0873Central
Region 14          5,171                                  14628.2330South
Region 15          7,543                                  774102.6192Central
Region 16          8,144                                  27333.5229Central
Region 17          8,097                                  33941.8753North
Region 18        10,165                                  94793.1689South
Region 19          8,042                                  55068.3970Central
Region 20          7,926                                  35544.7945North
Region 21          8,283                                  39647.8178North
Region 22          9,659                               1,136117.61112North
Region 23          7,095                                  934131.64143South
Region 24          7,271                               1,059145.65139South
Region 25        10,181                                  66765.5180North
Region 26          6,634                                  783118.03116South
Region 27          7,312                                  965131.97139South
Region 28          9,582                                  986102.9083North
Region 29          9,461                                  76080.3376South
Region 30          9,682                               1,013104.6398South
Region 31          8,639                                  921106.6193East
Region 32          8,500                                  994116.94151East
Region 33          9,296                                  64168.9561North
Region 34        14,693                               1,13076.9174Central
Region 35          5,780                                  928160.55165East
Region 36          8,533                               1,731202.86189East
Region 37          6,383                                  59493.06109Central
Region 38          7,206                                  65991.4585South
Region 39          6,453                                  36556.5654Central
Region 40          8,104                                  79898.4785Central
Region 41        12,790                                  73157.1566South
Region 42          7,270                                  825113.48114South
Region 43          5,804                               1,473253.79230East
Region 44          7,497                               1,094145.93117East
Region 45          6,022                                  41869.4172North
Region 46        15,982                                  76647.9346Central
Region 47          6,692                                  54080.6988North
Region 48        11,220                                  87077.5475North
Region 49        10,083                                  34634.3235North
Region 50          8,633                                  36842.6345South
Region 51          8,001                                  71188.8691East
Region 52          5,715                                  850148.73173South
Region 53          5,483                                  37768.7685South
Region 54          6,375                                  848133.02102East
Region 55          7,245                                  763105.31122North
Region 56          8,548                                  71984.1187North
Region 57          8,912                                  51257.4555East
Region 58          9,134                                  80488.02104Central
Region 59        10,427                               1,658159.01172South
Region 60          6,906                               1,073155.37168East
Region 61          6,871                                  34349.9243South
Region 62          7,559                               1,424188.38159South
Region 63          9,170                               1,015110.69121South
Region 64          7,980                                  27334.2143South
Region 65          8,397                               1,272151.48125North
Region 66          7,326                               1,085148.10135East
All region

533,149

 

515843227.633187 
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

 

Toshie_1-1670407576984.png

 

Anonymous
Not applicable

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

 

Toshie_0-1670408574182.png

 

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

Toshie_1-1670408729828.png

 

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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