Get column names and add a new measure

[jaweher899]

I've a table %apping between originator and column names in fact sales:

Originator	ColumnNames
EFY	CF_EFY
SH	CF_SH
NS
ZK	CF_ZK

and a table Factsales 

Originator	Client_Name	CF_ZK	CF_SH	CF_GOS	CF_EFY	Origination Credit USD
EFY	aaa	20			40	$1073
SH	bbb	13.33	40		20	$4451
NS	vvv	6.67	80			$1798
NS	ccccc	6.67	80			$1798
ZK	xxx	6.67	80			$719
ZK	vgrgtg	6.67	80			$899

I need to add a new measure: Check the corresponding columnnames for EFY in the table maapin: the result is CF_EFY, then CF_EFY* Origination Credit USD

Any help please?

[Anonymous · Accepted solution]

Hi  @jaweher899 ,

Here are the steps you can follow：

1. Power Query – Copy the table Factsales to form Factsale_Copy.

2. Check the following 5 columns – Transform – Replace Values.

Null - 0

3. Select the following 5 columns – Transform – Unpivot Colums.

Change the data format to Decimal Number.

4. Create measure.

Flag1 =
MAXX(FILTER(ALLSELECTED('Table'),'Table'[Originator] in SELECTCOLUMNS('Factsales',"1",[Originator])
),[ColumnNames])

Flag2 =
var _column=SELECTCOLUMNS('Factsales',"1",[Originator])
var _column2=SELECTCOLUMNS('Factsales',"2",[Client_Name])
var _sum1=SUMX(
    FILTER(ALL('Factsales_Copy'),
    'Factsales_Copy'[Originator] in SELECTCOLUMNS('Factsales',"1",[Originator])&&'Factsales_Copy'[Attribute] = [Flag1]
    && 'Factsales_Copy'[Client_Name]= MAX('Factsales'[Client_Name]))
    ,[Value])
return
_sum1
*
SUMX(FILTER(ALL(Factsales),'Factsales'[Originator]=MAX('Factsales'[Originator])&&'Factsales'[Client_Name]=MAX('Factsales'[Client_Name])),[Origination Credit USD])

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jaweher899]

Hi @Anonymous , I 've an issue with the total with the same report 

I post a new question here https://community.powerbi.com/t5/Desktop/wrong-total/td-p/2979800

Thank you