Filter to count the calculation using slicer from another table

[sinanalmac]

Hi   all,
I need to  preapare a report  about Availability. I 'm trying to Calculate count  product no  by  availability= 'yes ' / calculate  count  productcode   by availability all
I aplied this

CountYes = CALCULATE(count('Table'[ProductCode]),'Table'[Availability]="Yes")/CALCULATE(Count('Table'[ProductCode])) 

 when I filtered with this mesause is doesn't work preperly.
then I aplied this :

CountYes = CALCULATE(count('Table'[ProductCode]),'Table'[Availability]="Yes")/CALCULATE(count('Table'[ProductCode]),ALLEXCEPT('Table','Table'[Date].[Year],'Table'[Group],'Table'[Point type],'Table'[Region],'Table'[Week]))

After this Measure, I could filter   "Allexcept column" " data in the same table
but   I can't filter from  anatoher dimesion  table .
Is there any one  that knows  How can I do that
here is my power BI link  it  maybe help

https://www.dropbox.com/s/ccwcw6xmdwqdpky/Count.pbix?dl=0

here is my  realtionship

 best regards
sinan
 

[v-lionel-msft · Accepted solution]

Hi @sinanalmac ,

The relationship of your tables in the data model is right, but you have create wrong column and measure:

I have created a measure like this:

Measure = 
VAR x = 
CALCULATE(
    COUNT('Table'[ProductCode]),
    FILTER(
        'Table',
        'Table'[Availability] = "yes"
    )
)
VAR y = 
CALCULATE(
    COUNT('Table'[ProductCode]),
    ALLSELECTED(Week[WeekOrder])
)
RETURN
x/y

I don’t know that what does your ‘WeekOrder’ column want to calculate.
Is this what you want?

WeekOrder = 
RANKX (
        FILTER(
            Week,
            Week[Week Year] = EARLIER(Week[Week Year])
        ),
        Week[WeekNumber],
        ,
        ASC,
        DENSE
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sinanalmac]

hi  @v-lionel-msft  thank for your solution and your interested. 

Your  solution Turn on a light in my mind

Thank you for helping me?

I 've also  found  anather solution  regarding this.   I just  want to share  it
KEEPFILTERS AND REMOVEFILTERS functions are also  working.

CountYes = CALCULATE(count('Table'[ProductCode]),'Table'[Availability]="Yes")/CALCULATE(Count('Table'[ProductCode]),KEEPFILTERS(table[date].[Year])