conditional multiply

[shei7141]

Hi all,

I have a table which looks like the following

UniqueID	Sub	Total	OriginalDis	NewDis	NewRate
1	1a	500	10%	20%	Total x NewDis per Unique ID
1	1b		50%	40%
1	1c		40%	40%
2	2a	100	100%	100%

How can I calculate the NewRate column using dax?

Thanks in advance

[Anonymous]

HI @shei7141 

Form the limited information you have provided.

Here is what I would do.

NewRate = CALCULATE(SUM([TOTAL]*[NEWDIS]), ALLEXCEPT(TABLE,TABLE[UNIQUEID]))

and use this two columns in a matrix chart. 

[Anonymous]

Hey @shei7141 

Try the following. Please let me know if it works.

-JD

[shei7141]

Thanks J

sorry mate,

it does not recoganises any columns and I was expecting the followings results 

[parry2k]

@shei7141 you replies are very confusing, in the last reply you inked the values which were already possible from my first solution. kind of confusing what you actually want

[shei7141]

thanks mate

but with your suggestion also, the return value was 'blank' where the total value was not filled.

Ta,

Shei

[parry2k]

@shei7141 you need to revisit your question, put an excel file, and provide the calculation with the expected result. Your question is super simple and the reply has been already made but your return replies are making it super confusing. Help us to get help

[shei7141]

ok this is the story

MainItem	Total	Sub	OriginalDis	NewDis
A	100	a1	10%	50%
A		a2	15%	5
A		a3	20%	20
A		a4	5%	15
A		a5	50%	10
B	200	b1	30%	45
B		b2	25%	25
B		b3	45%	30
I created the measure for Revised Fee
NewTotal = SUMX ( VALUES ('Table'[UniqueID] ), CALCULATE ( SUM ( 'Table'[Total] ) ) * 1 )
I draw a table in Vizualisation and I get the following
MainItem	Total	Sub	OriginalDis	NewDis	NewTotal
A	100	a1	10%	50%	50
A		a2	15%	5%
A		a3	20%	20%
A		a4	5%	15%
A		a5	50%	10%
B	200	b1	30%	45%	90
B		b2	25%	25%
B		b3	45%	30%

the desirable outcome is

MainItem	Total	Sub	OriginalDis	NewDis	NewTotal
A	100	a1	10%	50%	50
A		a2	15%	5%	5
A		a3	20%	20%	20
A		a4	5%	15%	15
A		a5	50%	10%	10
B	200	b1	30%	45%	90
B		b2	25%	25%	50
B		b3	45%	30%	60

when apply the original suggestion (i.e. 

New Col = CALCULATE ( MAX ( 'Table'[Total] ), ALLEXCEPT ( 'Table', 'Table'[UniqueID] ) ) * 'Table'[NewDis]

I get the following 

MainItem	Total	Sub	OriginalDis	NewDis	NewTotal
A	100	a1	10%	50%	150
A		a2	15%	5%	15
A		a3	20%	20%	60
A		a4	5%	15%	45
A		a5	50%	10%	30
B	200	b1	30%	45%	135
B		b2	25%	25%	75
B		b3	45%	30%	90

[parry2k]

@shei7141 this what you shared that is not working but you are not sharing what you want? and also the original solution I posted, if you used that, what is not working? Your reply/requirement is still very unclear.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[shei7141]

sure - I updated my response and included the desirable outcome

[parry2k]

@shei7141 the solution I posted will get you this, isn't it? Did you try it?

[shei7141]

yes I tried both solution but they did not work

your first solution sumed the total and than applied the NewDis

second one only calculated the RevisedFee where Total had some number, 

[parry2k]

@shei7141 not exactly sure about your comment on the first one, why not put a pbix file with sample data and both the solution I provided and share it with me.

[amitchandak]

@shei7141 , what is expected output. Are these all column.

One solution can be a new column - but I doubt you need that

new column = [Total]*[NewDis]

[shei7141]

Thanks heaps

I want to calculate this as a measure and expecting the return value to be (Total x new Dist)

[shei7141]

also - my apologies, I forgot to mention, for each unique ID, the total value is only mentioned once and in the sub catergory the total is left blank

[parry2k · Accepted solution]

@shei7141 here is the formula, add new column

New Col = CALCULATE ( MAX ( 'Table'[Total] ), ALLEXCEPT ( 'Table', 'Table'[UniqueID] ) ) * 'Table'[NewDis] 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[shei7141]

thanks Perry,

It returned the sum of total for each Unique ID

I tried the following (i.e. before multiplying with the new distributional values)

= CALCULATE(

MAX(Tab[Total]),ALLEXCEPT(DatabaseTab,DatabaseTab[UniqueID]))

[parry2k]

@shei7141 I have no idea what you mean? Are you adding this a column or a measure? Why not you multiply as I expected, see screenshot below

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[shei7141]

sorry for the confusion

I am calculating a measure.

I want to return New Col (as a measure) = total (per unique ID) x NewDis

[parry2k]

@shei7141 change it to measure as below

New Measure = 
SUMX ( VALUES ('Table'[UniqueID] ), 
CALCULATE ( SUM ( 'Table'[Total] ) ) * 
CALCULATE ( MAX ( 'Table'[NewDis] ) )
) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡