topic Help with percentages in DAX Commands and Tips

Help with percentages

Neilt — Sat, 26 Mar 2022 16:30:01 GMT

Hi Community,

apologies if this seems like a noob question but in desperate need of help.

we have a Power BI dataset Already which tells me the records recorded over a monthly period, I even have a column in the dataset which tells me if that record has hit SLA or not, I have this value as simply HIT or MISS.

I have managed to work out using a table the percentage of hits vs the number of misses, drilled down over the last 6 month however cannot seem to work out how to not display the MISS data without it messing up the HIT data, can anyone offer any advice on what I am doing wrong?

also is there anyway for DAX to work out the percentage change month over month based on the HIT percentage?

many thanks in advance

Re: Help with percentages

bcdobbs — Sat, 26 Mar 2022 20:23:04 GMT

Can you share a santised pbix file with some example data, your model and what you have so far.

It sounds like you probably just need to include a REMOVEFILTERS inside calculate to prevent the filter context interfering. Hard to be more specific at the moment though.

Re: Help with percentages

Neilt — Sun, 27 Mar 2022 05:37:14 GMT

Hi Ben

Due to the confidental data in the pbix I cannot share unfortunetely

I can however show you the table I am getting back at the moment

Its pulling in the right amount of Miss and Hit data however if I use my filters to try remove the miss data my HIT data auto turns to 100% which is the issue, all I really want is the HIT data percentage displaying so 64.52 for this month 62.96 for last etc

Re: Help with percentages

tamerj1 — Sun, 27 Mar 2022 18:05:35 GMT

Hi Neilt

This might not be so accurate as I have no idea about your data structure but you may try one of the following

1. You can have a slicer with "HIT", "MISS" and use the following code

Percentage = VAR Numerator = COUNTROWS ( Table ) VAR Denominator = CALCULATE ( COUNTROWS ( Table ), ALLEXCEPT ( Table, Table[Date] ) ) VAR Result = DIVIDE ( Numerator, Denominator ) RETURN Result

The result percentage will be based on the slicer selection. If you select HIT it will give you the percentage of HIT's and same for MISS

2. code for HIT only

HIT Percentage = VAR Numerator = CALCULATE ( COUNTROWS ( Table ), Table[Hit SLA?] = "HIT" ) VAR Denominator = CALCULATE ( COUNTROWS ( Table ), ALLEXCEPT ( Table, Table[Date] ) ) VAR Result = DIVIDE ( Numerator, Denominator ) RETURN Result