Hide rows after calculating percent of grand total

[DolEgon22]

This seems like an easy question, but I'm not finding it. 

I have a simple set of data that shows RunStatus and Count of Asset. I can get the percent of grand total easily:

However, all I want to show is the Unknown RunStatus percentage in a simple data label visualization. Unfortunately, if I filter on just Unknown, I get 100%. Is there anyway to simply hide the other rows so that I can only show the Unknown percentage?

[bblais]

Try using a measure instead of the "Show Value As" function:

% GT = 
VAR CurrAssets = SUM(Table1[Asset])
RETURN CALCULATE(DIVIDE(CurrAssets,SUM(Table1[Asset])),ALL(Table1))

This first gets the current row's asset sum, then divides it by the grand total of assets regardless of what filters are applied.

[Eric_Zhang]

---

@bblais wrote:

Try using a measure instead of the "Show Value As" function:

% GT = 
VAR CurrAssets = SUM(Table1[Asset])
RETURN CALCULATE(DIVIDE(CurrAssets,SUM(Table1[Asset])),ALL(Table1))

This first gets the current row's asset sum, then divides it by the grand total of assets regardless of what filters are applied.

---

@bblais

No offence, but a little bit mistake in DAX? I guess you mean

% GT =
VAR CurrAssets =
    SUM ( Table1[Asset] )
RETURN
    DIVIDE ( CurrAssets, CALCULATE ( SUM ( Table1[Asset] ), ALL ( Table1 ) ) )

[bblais]

Actually, either formula works, but agreed that the form of yours is cleaner.

[DolEgon22]

Both are great solutions. However, I don't think I was clear on my original post. The Asset column is type 'String', so these measures don't work... I get an error:

Calculation error in measure 'Table1'[% GT]: The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String.

My intent is to count the # of rows for each asset's "RunStatus" (inherent in PBI and in my screenshot above), and then get a percentage of that count of only 1 of them--in this case, when RunStatus=Unknown. Ultimately, I want to show the Unknown percentage on a Card visualization (which the "Show Value As" function fails me on--when I filter to Unknown, it shows me 100%). 

Let me know if that's more clear.

Thanks again!

[bblais · Accepted solution]

Understood, didn't notice the Count of Asset in your screen shot.

You should be able to create a measure to do the count and then use it in the same way for % GT.  Something like this:

AssetCount = COUNT(Table1[Asset])

Then the % GT:

% GT = 
VAR CurrAssets = [AssetCount]
RETURN DIVIDE(CurrAssets,CALCULATE([AssetCount],ALL(Table1)))

Alternatively, you could add a calculate column, either in your query or in DAX that is simply = 1 and call that AssetCount.  Then you would be summing this column up to get your count per Run Status.

Calculated Column:
AssetCount = 1

Measure:
% GT = 
VAR CurrAssets = SUM([AssetCount])
RETURN DIVIDE(CurrAssets,CALCULATE(SUM([AssetCount]),ALL(Table1)))

[DolEgon22]

Closer! (sorry I'm a DAX noob) It's showing 1 instead of 100% (and 0 when I filter)... how do I change the value to percentage?

[bblais]

Choose the column you want to format in the fields list on the right, and then go to the modeling tab in the ribbon and change the format to percentage.

[DolEgon22]

...and a PBI noob as well apparently 

Thanks! I've got what I need!

[bblais]

No problem, happy to help.