Forum Discussion

sinanalmac's avatar
sinanalmac
Icon for Resolver I rankResolver I
6 years ago
Solved

Filter to count the calculation using slicer from another table

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
 

Need Help
Show and Tell
Tutorial Requests
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      sinanalmac
      Icon for Resolver I rankResolver I
      6 years ago

      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])