Calculate the difference from the MAX value in a column

[ganchevd]

Hi,

I have an issue with the MAX value formulas in Power BI. I use it quite simple in Excel but it is different in Power BI. So the Excel formula I use looks like: =C2/MAX($C$2:$C$27). which I use to measure all the customers to the one with highes revenue for me. Here is the sample table:

Customer	RevAnnual	Ranking
A	249 555	0.17
B	438 288	0.29
C	1 495 995	1.00
D	196 124	0.13
E	149 705	0.10
F	151 167	0.10
H	107 810	0.07
I	174 698	0.12
J	123 490	0.08
K	154 095	0.10
L	135 093	0.09
M	118 223	0.08
N	168 222	0.11
O	201 819	0.13
P	177 126	0.12
Q	695 892	0.47
R	145 866	0.10
S	283 475	0.19
T	158 290	0.11
U	307 196	0.21
V	114 665	0.08
W	218 940	0.15
X	134 489	0.09
Y	227 215	0.15
Z	117 036	0.08
AA	210 760	0.14

In the attached table the RevAnnual is a Measure column in my report. Can someone help me figure out how to do this? Thank you in advance

[PattemManohar · Accepted solution]

@ganchevd Here it is..

Ranking = TestMax[RevAnnual]/MAX(TestMax[RevAnnual])

 

[v-piga-msft]

Hi @ganchevd,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your desired output so that we could help further on it.

Best Regards,

Cherry

[ganchevd]

Well the desired output is what @PattemManohar showed and @Fcoatis in his fist proposal of a solution. Still it is far more complicated since I use direct link to the SQL database of MS DynamicsNAV and I have the information from three tables which i use to obtain the sample above. This was misleading to everyone who tried to help.

So. I have a table of Sales. There I have approximately 1m postings. I have a table Customer, which is linked to the Sales table by the uniqe CustomerNo. I have a third table which is the Differed Rev. And I made a measure to subtract from the differed rev from the sales, so that I'd be able to have a clear view over the revenue. In the visual table I'd shared I used as follows: Customer Name column from table customers and the RevAnnual measure. I am still not able to calculate the deviation of each customer revenue compared to the customer with the MAX revenue. It is easy in Excel but I'd like to be able to abandone these exports and justification of the data from NAv to Excel and make it life.

[PattemManohar · Accepted solution]

@ganchevd Here it is..

Ranking = TestMax[RevAnnual]/MAX(TestMax[RevAnnual])

 

[ganchevd]

OK! I've got it!

So the solution here is a compilation of what @PattemManohar and @Fcoatis proposed. And here is what I did:

1. Created a new DAX table, where I put all the data I need as Columns.

1.1. I created a table where I used DISTINCT(Customer[Customer]) formula;

1.2. I added a new column: RevAnnual = [RevAnnual], which was a measure;

1.3. I then I used the formula which @PattemManohar proposed. It took me good 2 minutes to figureout that it is Column, not Measure formula.

It works. Thanks everyone for the support. You're great.

[ganchevd]

OK Now I have an issue when I try to use the same approach when the values are % I try to divide the profitability % by the MAX profitability % in a column. Same scenario but not with absolute values and the result is not the one I require.

Any suggestions?

[Fcoatis]

Please send a sample of your data and the expected result.

Regards

[ganchevd]

Basically the same scenario but with %

Customer	Profitability %	Ranking
A	27.40%	0.61
B	25.80%	0.57
C	16.10%	0.36
D	13.60%	0.30
E	25.00%	0.56
F	36.00%	0.80
G	18.60%	0.41
H	-24.90%	-0.55
I	33.10%	0.74
J	22.80%	0.51
K	-0.50%	-0.01
L	7.60%	0.17
M	35.40%	0.79
N	8.30%	0.18
O	22.20%	0.49
P	-11.20%	-0.25
Q	24.10%	0.54
R	26.10%	0.58
S	25.30%	0.56
T	13.90%	0.31
U	21.50%	0.48
V	23.90%	0.53
W	25.70%	0.57
X	26.40%	0.59
Z	44.90%	1.00
AA	18.50%	0.41

[Fcoatis · Accepted solution]

[ganchevd]

It is not working with me. I did not mentioned something which I am sure is significant. the RevAnnual is a measure. not columnt from the table.

[Fcoatis]

Here you go:

https://drive.google.com/file/d/13HqVh84skLyscdvBtkRIHcFLZ_PW30MJ/view?usp=sharing

Regards

[ganchevd]

Hi I'm close to obtain the same result. Just a question why is this Sort column in the table you produced. Is it essential. If I remove it from your formula then it returns 1 for all of the customers.

[Fcoatis]

Hi @ganchevd,

The sort column is not essential. Its just for a matter to sort properly the Customer name. You can remove it. Be aware that you must remove all mentions to that column in the measure I showed you.

Best regards.

[ganchevd]

Yes it should be that simple if the data is in one and the same table. I shall start over since there are some complications which break my formula.

1. The data is a direct link to SQL database with multiple tables;

2. The column Customer is a column from the table Customers, which is linked 1: many with the table Revenue;

3. The column RevAnnual is in the table Revenue and the table I showed above it is an aggregated of all the postings by Customer. Or when I presnt the data in the tablle, attached above when I chose the column customer it is something like the subtotal in Excel.

I am not sure if I explained it but I cannot attach the exported raw data from the ERP SQL database

[Fcoatis]

@ganchevd I let me know if this suits you.

https://drive.google.com/file/d/1WRcRoCkEEZjPy5DbZCpGvZSO0tNfu3uH/view?usp=sharing

Regards

[LivioLanzo]

do you want to do this in a measure or calculated column ?

[ganchevd]

I'd like it in measure.

[LivioLanzo]

should be something like this:

=
IF (
    HASONEVALUE ( Table[Customer] ),
    DIVIDE (
        SUM ( Table[RevAnnual] ),
        CALCULATE ( SUM ( Table[RevAnnual] ), ALL ( Table[Customer] ) )
    )
)

[Fcoatis]

Just substitute max for sum:

Rank =
IF(
        HASONEVALUE(Customer[Customer]),
        DIVIDE(
            SUM(Customer[RevAnnual]),
            CALCULATE(MAX(Customer[RevAnnual]);ALL(Customer[Customer]))
       )
)

[ganchevd]

Now I try to modify this formula but I cannot use MAX argument for measure or something like this:

Rank =
IF(
        HASONEVALUE(Customer[Customer]),
        DIVIDE(
            [RevAnnual],
            CALCULATE(MAX([RevAnnual]);ALL(Customer[Customer]))
       )
)

I'm totally stuck. Thanks everyone for the help.