Calculated Column

[Anonymous]

Hi,

I want to check how do I calculate column based on different categories in a row

For eg.

Groups           Flow

sb003            159000                                                   GH003      77000

d2004            139000

ch001            156000                                                  No. of days = 10

f4500             200000

I have to write a formula like SB003 + D2004-CH001-F4500-GH003*(NO. OF DAYSINMONTH)

[Pragati11]

Hi @Anonymous ,

In Edit Query, select your GROUPS column, Pivot it.

Then normally create a DAX:

SB003 + D2004-CH001-F4500-GH003*(NO. OF DAYSINMONTH)

The values above will become your columns and directly use them to add.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

This does not seem like the format data is there in power BI

[Anonymous]

@amitchandak  Here's the sample data

[amitchandak]

@Anonymous , Try like

measure =
Var _max = maxx(allselected(Date),Date[date])
var _Min= eodate(_max,-1)+1
Var _noDay = datediff(_Min,_max,Day)
return
(calculate(sum(Table[total Value]), Table[group] in {"SB003","D2004"}) - calculate(sum(Table[total Value]), Table[group] in {"CH001","F4500","GH003"}))* _noDay

[Anonymous]

@amitchandak 

[amitchandak]

@Anonymous , my Mistake

it should be eomonth

var _Min= eomonth(_max,-1)+1

[Anonymous]

@amitchandak  I think it's still not picking up the variables. it has to do something with Var syntax

[amitchandak · Accepted solution]

@Anonymous , check the first line

Var _max = maxx(allselected(Dates),Dates[date])

[Anonymous]

@amitchandak  This works! Thank you so much