Checking relationships or measures between tables due to blank visuals

[akkitek]

Hi Everyone,
I'm stuck with a relationship or a DAX issue. I have a couple of verticles, "Compare Prospects" and "Compare Companies". I have built these as bookmarks and would like all of the visuals to work with these two bookmarks. For the ease of looking at data in the visuals I have converted the first two visuals as tables at the bottom. 

Compare Companies:
In this  bookmark, the user can use the "Company Slicer" and the input fields at the bottom are greyed out. For this company "120/80 MKTG" and when "Marketing" industry is selected, the visuals load properly, but when I change the industry or the company name, all the data vanishes. I need to be able to look at the data at a company and industry level. Basically I should also be able to compare with data from other industries. 

Compare Prospects:

In this bookmark/scenario - the company slicer is greyed out and we  have input boxes for the user to enter values into and the visuals changed based on the entered value. 

The calculation works fine but when we change the industry, the visuals take time to render and breaks up a little. These visuals are built using MSFT Charticulator. This, I am assuming happens becasue it is calculating for all of the companies. 

In some cases, when I switch the aggregation from "Don't summarize" to "First" the tearing is fixed but  in some cases the data vanishes.  

Here's the link to the sample file: https://drive.google.com/file/d/1S_n8QKAkL4C0PKM5PFchRBMDKfDVSFUs/view?usp=sharing

Ideally we would need to open up the bookmarks pane to view the issues. 

Any help is appreciated. 

Thanks @Ashish_Mathur !

[Ashish_Mathur]

Hi,

There's a maze of measures there.  I just checked the Rank Me measure.  Since the measure [ME Filter] returns 0 for Industry "Accounting", the measure returns blank.  This should be a start for you to check the underlying logic.

[akkitek]

Gotcha, any idea how I can change that? 

[Ashish_Mathur]

Hi,

Please check the logic of the ME Filter measure and make the appropriate correction.  Since i have not created the model, i will take a lot of time to dive into the measures there.

[akkitek]

Hi @Ashish_Mathur , Quick question: I have a measure called Rank which looks like this:

Rank =

VAR     _measure = SELECTEDVALUE('Measures Comparison'[Measure])

VAR     _value = CALCULATE([Value], FILTER(ALLSELECTED(Firms), Firms[FirmNm] = SELECTEDVALUE(Firms[FirmNm])))

VAR     _rank = RANKX(ALLSELECTED(Firms), [Value], _value, ASC, Skip)

VAR     _count = COUNTAX(ALLSELECTED(Firms), 1)

RETURN  IF(_count >= 10 && SELECTEDVALUE('Measures Comparison'[Measure]) <> BLANK(), DIVIDE(_rank, _count))

However in some cases the result of this measure can be greater than 100%. How do I tweak the formula in such a way that it returns which percentile this value falls in (0-100%)? Any ideas here? Thanks!

[Ashish_Mathur]

Hi,

Share some dummy data, explain the question and show the expected result.

[akkitek]

Sure thing @Ashish_Mathur ! 
So I have this "Rank Me" measure which returns 109% which I select "legal" industry. I want this measure to give me the percentile which this value belongs to, which means it should be within 0-100%. In this case its exceeding 100%. Writing a if statement to say if value exceeds 100, return 100 wouldn't be accurate since I need to calculate which percentile the value belongs to. 
Here's the link to the test file with dummy data: https://drive.google.com/file/d/1S_n8QKAkL4C0PKM5PFchRBMDKfDVSFUs/view?usp=sharing

[Ashish_Mathur]

Hi,

Please explore the PERCENTILEX() function.

[akkitek]

Ok. I'll look into it. Thanks!