Help with Replicating excel formula

[DummyBIDev]

Hi all

New member on here but long time browser. 

So a bit of background - i have a few excel documents which feed into a power bi dashboard. The task is now to make it a bit more robust so i have thought about replicating the excel files into Sharepoint Lists which will then feed into the power bi dashboard.

In order to do that, the excel files have alot of formulas which i need to replicate and i understand that these can't be done in Sharepoint but are possible in Power BI (correct me if i am wrong please)

The formula i have is

=IF(AND(COUNTIF(P6:AS6,"N/A")>=1,COUNTIF(P6:AS6,{"Green","Amber","Red"})*{1,1,1}=0),"N/A",IFERROR(SUM(COUNTIF(P6:AS6,{"Green","Amber","Red"})*{1,0.5,0})/SUM(COUNTIF(P6:AS6,{"Green","Amber","Red"})*{1,1,1}),""))

How easy would it be to replicate this in PowerBI and is my approach for creating sharepoint lists and feeding them into the power bi dashboard a sensible one?

Many Thanks

[Anonymous · Accepted solution]

Hi @DummyBIDev ,

As far as I know, Power BI supports us to calculate based on columns. According to your code in excel, I think your data should in same rows from P6 to AS6. If you want to convert your excel formula by Dax, I suggest you to transform your data model. 

Firstly add an index column in your table. If you have data from ROW 1 to ROW N, [Index] will help us to determind which row your data should be in data source. Then select [Index] column and Unpivot Other Columns. New table should look like as below. 

Measure:

Measure = 
VAR _COUNTNA = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Index] = 6&&'Table'[Value] = "N/A"))
VAR _GREEN = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Index] = 6&&'Table'[Value] = "Green"))
VAR _Amber = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Index] = 6&&'Table'[Value] = "Amber"))
VAR _Red = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Index] = 6&&'Table'[Value] = "Red"))
VAR _MAX = MAXX({_Amber,_Red,_GREEN},[Value]) *1
VAR _SUM1 = _GREEN *1 +_Amber *0.5 + _Red *0
VAR _SUM2 = _GREEN *1 +_Amber *1 + _Red *1
RETURN
IF(AND(_COUNTNA >=1, _MAX = 0),"N/A",IFERROR(DIVIDE(_SUM1,_SUM2),""))

Result is as below.

Best Regards,
Rico Zhou

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