Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Countrows with filters from current table applied only

Update: I've found a different way to organize my data, so I don't need this anymore

Hi,

 

Here's my PBI report with sample data: https://www.dropbox.com/s/9k0fth7qed7t33n/sample%20data.pbix?dl=0

 

So, here's the full set of data:

'Country of origin' comes from the 'Master Data' table (attribute table), while the other values come from the '2 Snacking Occasion' table. 

This is the model view:

My entries measure is simply a countrows of the 'master data' table.

% measure =
DIVIDE(
'Master Data'[Entries],
CALCULATE(
COUNTROWS('Master Data'),
ALLCROSSFILTERED('Master Data')
)
)
 

What I'm trying to get is if I have a slicer (field will be from master data table) applied, say for example, 'Europe & Americas' chosen for 'country of origin', the % by value should display as 80% (4 divided by 5) for the afternoon snacking category.

Basically the denominator portion of the divide function should count rows for all entries in the master data table without the filters from the '2 Snacking Occasion' table being applied.

 

This should work for all attributes chosen, e.g. gender, age, city, etc.

 

Thank you!

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi, I've actually added an update to my post. I've found a different workaround in my dataset, so I no longer need an answer to this. Thanks for looking into it though!

4 Replies

  • Anonymous , Try like

     

    % measure =
    DIVIDE(
    [Entries],
    CALCULATE(
    COUNTROWS('Master Data'),
    all('2 Snacking Occasion' )
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable

      Nope, that doesn't work. 😞 Still get the same values...

      • amitchandak's avatar
        amitchandak
        Super User

        Anonymous , The file is nor available at given link


        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.