Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
binayjethwa
Helper IV
Helper IV

How to calculate weight of Rows based in conditions

 

 

I have the following requirement where i am supposed to calculate the weight of each row in a table.

I need to calculate the Weight column which is 100/ count ( KPI type and KPI)

For example in below KPIType focus area is having only 1 KPI(Operation) so it would be 100/1= 100%

Similarly , KPI type BL KPI is having 4 KPI (ST,CC,SC,Spares) hence it would be 100/4 = 25% each. for KPIGroup as Reliability

 

Note : KPIType would have duplicates also hence we need to consider KPIGroup also in calculation.

 

Can someone help to find a way where i can add a calculated column for below condition. Or is there any other way we can achieve this. Thanks in Advance.

 

BLKPIGroupKPITypeKPIWeight
GenReliabilityFocus AreaOperation100%
GenReliabilityBL KPIST25%
GenReliabilityBL KPICC25%
GenReliabilityBL KPISC25%
GenReliabilityBL KPISpares25%
GenFinancialsBL Focus AreaSubstainability100%
GenFinancialsBL KPIControllable33.33%
GenFinancialsBL KPIMargin33.33%
GenFinancialsBL KPIFuel33.33%
GenBusinessBL Focus Areaspend100%
GenFinancialsBL KPIcost50%
GenFinancialsBL KPIStrategy50%
10 REPLIES 10
TomasAndersson
Solution Sage
Solution Sage

Hi!
You should be able to use EARLIER() and a few filter conditions to make it work. Try this:

 

Weight = 
DIVIDE(  //divide
    100, //100 by..
    CALCULATE(
        count('Table'[KPIType]), //the number of KPI types...
        FILTER(ALLSELECTED('Table'),
            'Table'[KPIType] = EARLIER('Table'[KPIType]) && //...of current KPI type
            'Table'[KPIGroup] = EARLIER('Table'[KPIGroup]) //That also belong to the current KPIGroup
        )
    )
)

 

 

 

 

@TomasAndersson

I have similar requirement where i need to show weight based on KPI group..

But again KPI group will have KPIType, KPI,BI also into consideration.

So here the total number of KPI group / total count

 

KPI GroupWeight
Reliability, Service Resilience & Security of Supply20%
Financial Efficiencies 20%
Business Continuity 20%
Sector/ Company Restructuring20%
Internal Control20%

 

I tried the similar way which was suggested and it is not working.

Can you please help here please.

 

 

Hi,

I'm not sure I quite understand what you're looking for, could you help me expand your question?

Does the data table look the same as in the first example? But weight according to KPI group instead? 

It would be great if you can have some example data and expected result as in the first case  

@TomasAndersson 

 

So i will have two tables to display, the one which we saw earlier as below.

So here we need to create another table which is calculating average of individual KPI Type separately. Please see below as example.

 

BLKPIGroupKPITypeKPIWeightVar
GenReliabilityFocus AreaOperation100%0.1
GenReliabilityBL KPIST25%0.11
GenReliabilityBL KPICC25%0.12
GenReliabilityBL KPISC25%0.13
GenReliabilityBL KPISpares25%0.14
GenFinancialsFocus AreaSubstainability100%0.15
GenFinancialsBL KPIControllable33.33%0.16
GenFinancialsBL KPIMargin33.33%0.17
GenFinancialsBL KPIFuel33.33%0.18
GenBusinessFocus Areaspend100%0.1
GenFinancialsBL KPIcost50%0.19
GenFinancialsBL KPIStrategy50%0.2

 

Final Output table :

 

KPIGroupAverageWeight
Reliability0.333.33%
Financials0.52533.33%
Business0.133.33%

 

sample Formula to calculate individual Avg of reliability.

 

Focus area Var (From first table)=sum(0.1) = 0.1

BL KPI Var = sum (BL KPI ( (0.11+0.12+0.13+0.14)) = 0.5

Average = Avg(0.1,0.5) = 0.3 

Reliability Avg Reliability Avg = AVERAGE( SUM (Var for Focus Area) (0.1), SUM( Var for BL KPI(ST,CC,SC,Spares)(0.11+0.12+0.13+0.14)Average(Sum(focus Area) + Sum(BL KPI ))

 

Let me know if this is clear. Thanks in advance. Please do help.

Hi,
I'm still not entirely sure of what we're calculations. I don't really see how the numbers in the Average column in the Output table are averages. 

But I made an attempt at recreating the Final Output table in the linked file. I hope it at least helps you coming closer to your solution. I created a new table for the output table, but it could probably be done just using measures in the original table as well, and let me know if you want me to try that road instead. 

@TomasAndersson 

Please find below screenshot of two tables which are needed for output.

 

Keep in mind that there can be multiple KPIgroup , KPI , KPI type as mentioned in the initial table posted on the very first of this post. hope this helps.

binayjethwa_0-1671633431442.png

 

This is sample calculation for Reliability. 

I see you have taken two tables , table1 and table, But i have all the values in single table only.

In the earlier function  and other functions you have taken table and not table 1. 

Yeah, sorry that was a typo from my part. All should refer to the same table, the table you have your data in.

Thank you very much. It worked.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.