Getting % when selecting a Filter within Power BI

[Aguilucho24]

Hello BI Community,

I have a power BI sheet which has different options of filters. I want to know formula of total lines in report divided by choosen filter. 

Example, I have Open Order Report and within this report I choose orders FILTERED IN DROPDOWN with a column for orders on HOLD STATUS. I want to know % of lines on HOLD STATUS of complete open order list. Can you advise DAX formula?

Thank you 

[Aguilucho24]

Hello 

I have filter one with HD checkbox and other with NO HD checkbox. I used this formula and it works when I check NO HD regarding percentage but does not work when I check HD. I first got amount of lines on HD status and then try to get %. When I click on HD the % was given me 100% which is not accurate.

formula 1) Rows HOLD STATUS = CALCULATE(COUNTROWS('Open orders - AMERICAS'),'Open orders - AMERICAS'[Accounting status] ="HD")

formula 2) % Rows on HOLD STATUS = DIVIDE([Rows HOLD STATUS],(DISTINCTCOUNT('Open orders - AMERICAS'[Sales Order + Souriau Line ])),0)

In addition beside of  case above, is there a way to get % when I select data from specific table related to what I click when an option within  a filter.

example: I have table called Analysis code and within this table I choose "Pending". I want to get % of what is pending for total amount under table called analysis code which have pending and may others words.

[Aguilucho24]

Hello MarcosVin

Thanks for information....

Question, instead of choose a specific status such as HOLD. Is there opcion when I choose different slicer instead of only looking at HOLD. Example, in one moment I click on HOLD and gives me %. If then I decide to click on Past Due it give me %. The formula you provided only impacts slicer for HOLD STATUS.

Basically, trying to avoid to create for each slicer filter a formula.

[marcosvin]

Hi!

To pass the selected filter to the metric you can use the SELECTEDVALUE function. It would look like this:

Rows STATUS = CALCULATE(COUNTROWS(Fact_Table),Orders[Status]=SELECTEDVALUE(Orders[Status]))

[Aguilucho24]

I tried and it did not work. Let me explain a more details. I have several slicers.

1) Report Date slicer = I choose in this filter report one file or many to get data 

2) Entity Slicer = I then choose one or many entity which I want review t

3) The next steps I choose as needed and its where I want to see % of amount of lines open in entity vs info below.

      - IF I choose Accouting Status  Slicer = I want to see % which are on HD status or not.

      - IF I  decide to choose past due instead  of Accounting slicer =  I want to see % which are past due.

Keep mind % is based on total in Entity. 

[marcosvin]

Hello!

If I understand correctly you first need the overall total count of rows in the table, then the total rows with status = HOLD and then the percentage of these rows in relation to the grand total. When selecting an order in the slicer, it will show the percentage of that order with status = HOLD in relation to the grand total.
If so, you can create the metrics in Power BI like this:

Total rows = CALCULATE(COUNTROWS(Fact_Table),ALL(Fact_Table))

Rows HOLD STATUS = CALCULATE(COUNTROWS(Fact_Table),Orders[Status]="HOLD")

% Rows on HOLD STATUS = DIVIDE([Rows HOLD STATUS],[Total rows],0)