Filter data not showing correct results in card

[o59393]

Hi all

I created a dax function to calculate the percentage of projects under high risk, medium risk and low risk.

The metric works ok when nothing is selected or I select a country for example, however when I use the slicer and filter by the type of risk (high risk, medium or low), the percentages results in the cards make no sense.

The table used is:

What I would like to have is, if i select high risk in the slicer, then 100% of HR in the card and the rest show 0%, if I select medium risk in the slicer, the card should 100% for medium risk and 0% for the rest, and so on.

Thanks!

[v-shex-msft]

HI @o59393 ,

Can you please share some sample data with your measure formula? It is hard to test without any sample data.

How to Get Your Question Answered Quickly  

In addition, you can try to use the following measure which will dynamic changes based on your selection:

Measure =
CALCULATE ( COUNT ( Heatmap[Risk] ), VALUES ( Heatmap[Risk] ) )
    / COUNT ( Heatmap[Risk] )

Regards,

Xiaoxin Sheng

[o59393]

hi!

Here you go the sample data

https://www.mediafire.com/file/ipnnoa6ng49gsx6/query_%288%29.xlsx/file

I tried that formula and still game me high %, above 100%

Thanks 🙂 

[v-shex-msft · Accepted solution]

Hi @o59393 ,

You can try to use the following measure formulas:

LR =
IF (
    "Low Risk" IN VALUES ( 'Table'[Risk] ),
    CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "Low Risk" )
        / COUNT ( 'Table'[Risk] ),
    0
)
MR = 
IF (
    "Medium Risk" IN VALUES ( 'Table'[Risk] ),
    CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "Medium Risk" )
        / COUNT ( 'Table'[Risk] ),
    0
)
HR = 
IF (
    "High Risk" IN VALUES ( 'Table'[Risk] ),
    CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "High Risk" )
        / COUNT ( 'Table'[Risk] ),
    0
)

Regards,

Xiaoxin Sheng

[o59393]

hi!

thanks for the help

I got an error:

The syntax for 'MR' is incorrect. (DAX(IF("Low Risk" IN VALUES(Heatmap[Risk]),CALCULATE(COUNT(Heatmap[Risk]),Heatmap[Risk]="Low Risk")/COUNT(Heatmap[Risk]),0)MR = IF("Medium Risk" IN VALUES(Heatmap[Risk]),CALCULATE(COUNT(Heatmap[Risk]),Heatmap[Risk]="Medium Risk")/COUNT(Heatmap[Risk]),0))).

Thanks for the help!

[v-shex-msft]

Hi @o59393 ,

In fact, I attached three measures, you need to create three measures fields to stored these formulas.
Regards,

Xiaoxin Sheng

[o59393]

Hi

Apologies for my ignorance, how do i create the measure fields? Cant see the attachment.

Can you please share how to create them?

Thanks!

[o59393]

@v-shex-msft  I created the DAX with the 3 metrics but I still get the same error. Did you do something additional? Can you please shere the pbi.

Thanks!

[v-shex-msft]

Hi @o59393 ,

I attached the sample file below, you can try it if it works on your side.

Regards,

Xiaoxin Sheng

[lc_finance]

Hi @o59393 ,

could you share a sample Power BI file?

That will make it easier to help you

LC

[o59393]

hi!

Surem here you go.

https://www.mediafire.com/file/ipnnoa6ng49gsx6/query_%288%29.xlsx/file

Thanks 🙂