Can you tell me please how to write this MEASURE?

[Anonymous]

Hello

I have the following pbix:

https://ufile.io/pw9toyi6

In the dashboard, you can see a table with two columns: KPI and ITEM.

What I want, is to add a third column that will calculate the KPI for the respective ITEM.

The KPIs are self-explanatory and they are basically counting amounts that are <10 etc.

Can you help me please write that measure?

I want then to display a graph with each KPI and their total value (total number of amounts counted).

Thanks!

[aj1973]

Hi @Anonymous 

here is your measure

NO OF TRANSACTIONS =

IF (

SELECTEDVALUE ( KPIs[KPI] ) = "AMOUNT BETWEEN 10 and 15",

CALCULATE (

COUNTROWS ( TRANSACTIONS ),

FILTER (

TRANSACTIONS,

TRANSACTIONS[ITEM] = SELECTEDVALUE ( KPIs[ITEM] )

),

TRANSACTIONS[AMOUNT] >= 10 && TRANSACTIONS[AMOUNT] <= 15

),

IF (

SELECTEDVALUE ( KPIs[KPI] ) = "AMOUNT HIGHER THAN 15",

CALCULATE (

COUNTROWS ( TRANSACTIONS ),

FILTER (

TRANSACTIONS,

TRANSACTIONS[ITEM] = SELECTEDVALUE ( KPIs[ITEM] )

),

TRANSACTIONS[AMOUNT] > 15

),

IF (

SELECTEDVALUE ( KPIs[KPI] ) = "AMOUNT LOWER THAN 10",

CALCULATE (

COUNTROWS ( TRANSACTIONS ),

FILTER (

TRANSACTIONS,

TRANSACTIONS[ITEM] = SELECTEDVALUE ( KPIs[ITEM] )

),

TRANSACTIONS[AMOUNT] < 10

),

BLANK ()

)

)

)

You might want to adjust the inclusivity of 10 and 15 in the formula as you wish.

By the way i already wrote a measure same to this one in my previous Sample if you still want to use it.

Regards

[Anonymous]

Hi @aj1973 

Thanks but the graphs do not work with that measure.

I cannot create a graph of the KPIs in x and the MEASURE sum in the y axis. It is very frustrating.

[aj1973]

[Anonymous]

@aj1973  Thanks but it does not work for me. It's really frustrating.

Please find the my pbix version here: https://ufile.io/ci5bve9h

Any idea please?

[aj1973]

Change the visual and click to combine

Here is a gift for all the pain I have been through LOL

https://app.powerbi.com/view?r=eyJrIjoiMjc2N2ZkZmYtMTNjYi00N2Q0LWE4MmUtYjhiY2E4NTQxZGM1IiwidCI6IjgzN...

Just kidding

Regards

[Anonymous]

@aj1973 

Thanks but I do not want it to show 'by KPI and ITEM', I want it to display only 'by KPI'!

This is really a nightmare!

[harshnathani · Accepted solution]

Hi @Anonymous ,

You just need to create a Calculated Column in your Transactions Table.

KP =
SWITCH (
    TRUE (),
    TRANSACTIONS[AMOUNT] > 15, "AMOUNT HIGHER THAN 15",
    TRANSACTIONS[AMOUNT] >= 10
        && TRANSACTIONS[AMOUNT] <= 15, "AMOUNT BETWEEN 10 and 15",
    TRANSACTIONS[AMOUNT] < 10, "AMOUNT LESS THAN 10",
    "NO KPI"
)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Thanks, it's a good idea.

However, things are a bit more complicated.

The actual criteria overlap. So it is not x<10, 10<x<15 and x>15.

The actual criteria are like x<10, x<15, x>15.

So the calculated column cannot contain one KPI. Each transaction can be part of more than one KPIs.

I think this is something PowerBI developers need to look into.