How to Create a Measure to Calculate % value by Row Total

[SandoLokuge]

Hi,

i am very new to PowerBi and DAX and self teaching how to use it for my work.

i have to track the KPI according to the work site and is haviing issues trying to get the % value of KPI with respct to the row toal of clients.

the Data looks like this

Site	Clients Achived KPI	Total Clients	% Clients
AAAA	15	30
BBBB	78	116
CCCC	21	33

I used the folwing Mesure to calculate Clients Achive KPI 1

Clients Achived KPI=CALCULATE(COUNTA('KPI_01'[KPI 1]), 'KPI_01' [KPI 1] IN {"Achived"})

I used The follwing Measure to calculate Total Clients

Total Clients=CountA(KPI_01[ClientID])

How do i calcualte the % clients who achived KPI as per Site

i did this using Pivots in Excel, would like to know how to do this in Power BI

tahnks inadvance

[Anonymous]

Hi @SandoLokuge ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @SandoLokuge ,

According to my understand, you want to calculate the % of Achieved KPI 1 / total clients based on each Site, right?

You could use the following formula:

Achived Count =
CALCULATE (
    COUNTROWS ( 'KPI_01' ),
    FILTER (
        ALL ( KPI_01 ),
        'KPI_01'[Site] = MAX ( 'KPI_01'[Site] )
            && 'KPI_01'[KPI 1] = "Achived"
    )
)
/ CALCULATE ( COUNTROWS ( 'KPI_01' ), ALLEXCEPT ( KPI_01, KPI_01[Site] ) )

My visualization looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[amitchandak]

@SandoLokuge , a new measure like

divide([Clients Achived KPI],[Total Clients])

[SandoLokuge]

Hi Amit,

I tried using the divide fuction but it gives me 1 in every row.

i need the clients who achived the KPI divided by total clients for that row

thank you

[amitchandak]

@SandoLokuge ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[SandoLokuge]

Hi Amit,

The Raw Data Looks like this for KPI 1

Clients ID	Site	KPI 1
111	AAAA	Achieved
222	BBBB	Not Achieved
3333	AAAA	Achieved
444	CCCC	Achieved

I have another Table for all the sites so that i can cross refrence for other KPIs im tracking per site

SITE

AAAA

BBBB

CCCC

Hope this makes sense