Help with Replicating excel formula

DummyBIDev — Tue, 26 Jul 2022 09:23:25 GMT

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

Re: Help with Replicating excel formula

Anonymous — Fri, 29 Jul 2022 08:04:50 GMT

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.

topic Re: Help with Replicating excel formula in Developer

Help with Replicating excel formula

Re: Help with Replicating excel formula