Forum Discussion

Joakim_gg's avatar
Joakim_gg
Frequent Visitor
4 years ago
Solved

Convert Excel formula to DAX

Hi,

 

As I'm fairly new to Power BI I have a formula in Excel which I would like some input on how to use in Power BI. 

 

The formula is as follows: 

 

=IF(COUNT(I7:Q7)>0;(((IF(I7="";0;I7))+(IF(J7="";0;J7))+(IF(L7="";0;L7))+(IF(N7="";0;N7))+(IF(O7="";0;O7))+(IF(K7="";0;(5-K7)))+(IF(M7="";0;(5-M7)))+(IF(P7="";0;(5-P7)))+(IF(Q7="";0;(5-Q7))))/COUNT(I7:Q7));"")

 

Any help is much appreciated. Thanks!

Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Joakim_gg ,

    If you want to achieve it using DAX in Power BI, you need to create a reference unpviot table like this in Power Query first:

     

    Create a calculated column like this:

    Result = 
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Reference table' ),
        FILTER (
            'Reference table',
            'Reference table'[Category] = EARLIER ( 'Table'[Category] )
        )
    )
VAR A =
    IF ( [Column1] = BLANK (), 0, [Column1] )
VAR B =
    IF ( [Column2] = BLANK (), 0, [Column2] )
VAR C =
    IF ( [Column4] = BLANK (), 0, [Column4] )
VAR D =
    IF ( [Column6] = BLANK (), 0, [Column6] )
VAR E =
    IF ( [Column7] = BLANK (), 0, [Column7] )
VAR F =
    IF ( [Column3] = BLANK (), 0, 5 - [Column3] )
VAR G =
    IF ( [Column5] = BLANK (), 0, 5 - [Column5] )
VAR H =
    IF ( [Column8] = BLANK (), 0, 5 - [Column8] )
VAR I =
    IF ( [Column9] = BLANK (), 0, 5 - [Column9] )
VAR result =
    IF ( _count > 0, ( A + B + C + D + E + F + G + H + I ) / _count, BLANK () )
RETURN
    result

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    Hi Joakim_gg ,

    If you want to achieve it using DAX in Power BI, you need to create a reference unpviot table like this in Power Query first:

     

    Create a calculated column like this:

    Result = 
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Reference table' ),
        FILTER (
            'Reference table',
            'Reference table'[Category] = EARLIER ( 'Table'[Category] )
        )
    )
VAR A =
    IF ( [Column1] = BLANK (), 0, [Column1] )
VAR B =
    IF ( [Column2] = BLANK (), 0, [Column2] )
VAR C =
    IF ( [Column4] = BLANK (), 0, [Column4] )
VAR D =
    IF ( [Column6] = BLANK (), 0, [Column6] )
VAR E =
    IF ( [Column7] = BLANK (), 0, [Column7] )
VAR F =
    IF ( [Column3] = BLANK (), 0, 5 - [Column3] )
VAR G =
    IF ( [Column5] = BLANK (), 0, 5 - [Column5] )
VAR H =
    IF ( [Column8] = BLANK (), 0, 5 - [Column8] )
VAR I =
    IF ( [Column9] = BLANK (), 0, 5 - [Column9] )
VAR result =
    IF ( _count > 0, ( A + B + C + D + E + F + G + H + I ) / _count, BLANK () )
RETURN
    result

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Hello, welcome to the forum.

    Can you repost your question please?

    Please provide a small amount of sample data and the desired output (with an explanation of how to get there)