Percentage of occurence in filtered rows

[Anonymous]

Hi,

I'm trying to display in a table 4 rows containing :

1. The UserAgent responsible for the hit on my website
2. The number of times this UserAgent created a hit (i.e. the number of rows in which it appears)
3. The percentage of hits attributed to this UserAgent globally
4. The percentage of hits attributed to this UserAgent among the other UserAgents that have not been filtered out

I have other visualizations on my report that allow me to filter out some UserAgents.

Here are my calculated measures and columns :

Measures :

• TotalHits = CALCULATE(COUNTROWS('hits-store');ALL('hits-store'))
• Tot = CALCULATE(COUNTROWS('hits-store');ALLSELECTED('hits-store'))

Columns :

• Global = 1/[TotalHits]
• Filtered = 1/[Tot]

I have the expected results when I don't filter anything :

Data without any filters

But when I filter out some UserAgents, the Filtered and Global columns return the same results !

(the Filtered column for the first UserAgent should display 8,6%, and should add up to 100%) :

Data with filters

I even tried to display my Tot and TotalHits measures to see if they were wrong :

They are not.

I'm not a DAX expert, am I missing something here ?

[chrisu]

I think this has something to do with how ALLSELECTED() works.  Have you tried changing your Tot calculate filter to ALLSELECTED(UserAgent)?

More information at http://www.sqlbi.com/articles/understanding-allselected/

[Anonymous]

Hi chrisu, thanks for taking the time.

I tried your solution, but it doesn't change a thing.

I've tried various formulas after reading you article, but nothing really seems to work.

I can't seem to be able to keep the filter context of my page using ALLSELECTED() because it is inside a division (which adds one layer of calculation to my table, fuzzing my context filters in my page).

I'm not entirely sure I grasped everything in your article perfectly, so I may be talking nonsense.

[Sean]

@Anonymous how is you data organized? Can you post an example?

Is it like this?

EDIT: From the above table is this the result you are looking for?

[Anonymous]

@Sean this is almost exactly what I'm trying to accomplish. The results you have are what I want.

Except my UserAgents are filtered out by another Table (called references) that contains the names of the UserAgents I want to filter out.

I have only one relationship in my model.

This is the filter I use on my report, which displays the values of 'references'[Column1.Name] :

I probably should have mentioned that earlier ...

[v-sihou-msft]

@Anonymous

If you have built the relationship between User Agent and reference name, you can just build two slicers in your report. The User Agent will be cascaded based on the reference name selection. Then you can just COUNTROWS() the 'hits-store' divide by ALL() and ALLSELECTED() calculation.

Measures:

Hits = CALCULATE(COUNTROWS('hits-store')
TotalHits = CALCULATE(COUNTROWS('hits-store'),ALL('hits-store'​))
Tot = CALCULATE(COUNTROWS('hits-store'),ALLSELECTED('hit​s-store'))
Filtered = [Hits]/[TotalHits]
Global = [Hits]/[Tot]

[Anonymous]

 Hi @v-sihou-msft,

I have tried to use all the measures you recommended but it gives me an error when I try to input the last formula :

I'm not quite sure what exactly you meant by having two slicers (I don't get what is the point ?) but here is a screenshot of the whole report :

I can't display Global in the table because the formula is invalid, but "Filtered" doesn't return the expected value.

[v-sihou-msft]

@Anonymous

The error message indicates that the [Global] and [Filtered] has referenced each other so that it will detect circular dependency. Please check the expression in [Filtered].

The two slicers means one slicer for UserAgent selection, the other slicer for referenced name. When you select in reference name slicer, the UserAgent will be cascaded(selected automatically).

Regards,

[Anonymous]

Hi @v-sihou-msft,

I have in my file :

Hits = CALCULATE(COUNTROWS('hits-store'))

TotalHits = CALCULATE(COUNTROWS('hits-store');ALL('hits-store'))

Tot = CALCULATE(COUNTROWS('hits-store');ALLSELECTED('hits-store'))

Filtered = [Hits]/[TotalHits]

Global = [Hits]/[Tot]

If I'm not mistaken Global and Filtered do not reference each other in those formulas. This might be some kind of weird behaviour of DAX ?