Fidn the percentgae of the 4 colums value

[renudp]

Hi Bi Experts,

Need your help to find the values for the 4 columns, which will be aggregated for every month. The dataset is as follows: need to find the diffrenc of percantge for every month 

Alert Name	24-Jan	24-Feb	24-Mar	24-Apr
Failed to Connect to Computer	405	444	314	186
Health Service Heartbeat Failure	712	559	559	664
IP address conflict	1	1	1	2
Logical Disk Free Space is low	131	140	138	158
No ping reply	96	52	52	51
Percentage Logical Disk Free Space is low	973	1089	1035	856
Windows Service Stopped	96	103	103	1

[v-kongfanf-msft · Accepted solution]

Hi @renudp ,

You can try below formula to create measure:

% =
VAR month_ =
    MAX ( 'Table (2)'[Attribute] )
VAR next_ =
    CALCULATE (
        SUM ( 'Table (2)'[Value] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[Alert Name] = MAX ( 'Table (2)'[Alert Name] )
                && MONTH ( 'Table (2)'[Attribute] )
                    = MONTH ( month_ ) - 1
        )
    )
RETURN
    IF (
        ISBLANK ( next_ ),
        BLANK (),
        DIVIDE ( next_ - MAX ( 'Table (2)'[Value] ), next_ )
    )

Best Regards,
Adamk Kong

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

[renudp]

Hi Adamk Kong,

Apologies for the delayed response. I will try this solution and keep you posted

Regards

Renu

[DataNinja777]

Hi @renudp 

You can produce your required output in the following manner as an example.  

I attach an example pbix file.  

[hackcrr]

Hi, @renudp 

In Power BI, if you want to calculate the percentage difference from month to month (e.g. from January to February, February to March, etc.), you need to calculate the percentage change between neighboring months for each "Alert Name". Here are the steps on how to do this in Power BI:

In Power BI's Data view, you can add a new calculated column to store the percentage variance for each month. Since Power BI does not have a direct Percentage Difference function, you need to calculate it manually. Assuming your original data table is named Alerts and you want to calculate the percentage difference from January to February, you can add a new column using the following DAX formula:

Jan_to_Feb_Diff =   
VAR FebValue = [24-Feb]  
VAR JanValue = [24-Jan]  
RETURN  
IF(  
    JanValue <> 0,  
    DIVIDE((FebValue - JanValue), JanValue, 0) * 100,  
    BLANK()  
)

You can then repeat this process for the other month pairs (February through March, March through April, etc.), adding a new calculated column for each pair.

Best Regards,

hackcrr

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

[renudp]

Hi Hackcrr,

i am getting the error while creating the column with table name and with out table name 

[hackcrr]

Hi, @renudp 

Thank you very much for your reply. You can try the following DAX code:

Jan_to_Feb_Diff =   
VAR FebValue = SELECTEDVALUE('SCOM'[Feb-24])  
VAR JanValue = SELECTEDVALUE('SCOM'[jan-24])  
RETURN  
IF(  
    JanValue <> 0,  
    DIVIDE((FebValue - JanValue), JanValue, 0) * 100,  
    BLANK()  
)

The main reason for your error is that you are referencing columns directly in the variables of the measure, which is not allowed in DAX. Therefore, if we want to refer to columns in the metric we can use some aggregation functions (MIN, MAX, SUM) or SELECTEDVALUE functions, etc. Direct references to columns in DAX can be in the process of creating a calculate column.

Best Regards,

hackcrr

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

[renudp]

Hi Hackcrr,

Thanks your swift responce, gertting blank values in cloumn 

Regards

Renu

[renudp]

Hi hackcrr,

Thanks for the quick responce and solution, i need the this will continue to till Dec 2024 and need to add the consolidated value in Table 

Regards

Renu