Forum Discussion

Marifah's avatar
Marifah
New Member
6 years ago
Solved

graph interactions with DISTINCTCOUNT/average function


**not all tables,columns and fields are here to stay. These pictures were taken just I was messing around and trying stuff.



Hey everyone ^^

I'm quite new to the powerbi/query reporting. I'm learning stuff through solving problems as I stumble upon them. Because of the reason I have not been taught systematically; I may have mixed up concepts and functions. Sorry for being "probably" hard to understand.


Basically, I want to create 2 different graphs showing different months which is selected via 2 different slicer, that's why I've created duplicate month table. But here is the thing; I also need a table which shows average of these 2 graphs seperately. But when I try to use DISTINCTCOUNT function to divide the total of these graphs to the selected month count. I get tons of problems. 
I think that's because DINSTINCTCOUNT counts related Date table's values and not duplicated/seperated Month1, Month2 tables. For example If I select first month in first graph and second month in second graph, average value goes to zero for all rows.

Here are my measure functions  : 

 

 

 

Ortalama 1 = SUM(Liste[Karşılaştırma - 1])/DISTINCTCOUNT('AyTablo'[YA])

Ortalama 2 = SUM(Liste[Karşılaştırma - 2])/DISTINCTCOUNT('AyTablo2'[YA])

 

 

 



What am I doing wrong? I've tried to find a solution on other questions and guides but got flooded by same unnecessary and basic instructions.

  • Hi Marifah ,

     

    Please update your measure as below.

    Ortalama 1 = CALCULATE(SUM(Liste[Karşılaştırma - 1]),ALLEXCEPT(Liste,Liste[Odeme]))/DISTINCTCOUNT('AyTablo'[YA])
    Ortalama 2 = CALCULATE(SUM(Liste[Karşılaştırma - 2]),ALLEXCEPT(Liste,Liste[Odeme]))/DISTINCTCOUNT('AyTablo2'[YA])

     

  • Marifah's avatar
    Marifah
    6 years ago

    Thanks for the reply!


    This was not updating the Total.
    I've just added AyTablo[YA] to the ALLEXCEPT in order to let the Total change as well.

    As below:

    Ortalama 1 = CALCULATE(SUM(Liste[Karşılaştırma - 1]);ALLEXCEPT(Liste;Liste[Ödeme Kategorisi];AyTablo[YA]))/DISTINCTCOUNT('AyTablo'[YA])
    
    
    
    Ortalama 2 = CALCULATE(SUM(Liste[Karşılaştırma - 1]);ALLEXCEPT(Liste;Liste[Ödeme Kategorisi];AyTablo2[YA]))/DISTINCTCOUNT('AyTablo2'[YA])



2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support

    Hi Marifah ,

     

    Please update your measure as below.

    Ortalama 1 = CALCULATE(SUM(Liste[Karşılaştırma - 1]),ALLEXCEPT(Liste,Liste[Odeme]))/DISTINCTCOUNT('AyTablo'[YA])
    Ortalama 2 = CALCULATE(SUM(Liste[Karşılaştırma - 2]),ALLEXCEPT(Liste,Liste[Odeme]))/DISTINCTCOUNT('AyTablo2'[YA])

     

    • Marifah's avatar
      Marifah
      New Member

      Thanks for the reply!


      This was not updating the Total.
      I've just added AyTablo[YA] to the ALLEXCEPT in order to let the Total change as well.

      As below:

      Ortalama 1 = CALCULATE(SUM(Liste[Karşılaştırma - 1]);ALLEXCEPT(Liste;Liste[Ödeme Kategorisi];AyTablo[YA]))/DISTINCTCOUNT('AyTablo'[YA])
      
      
      
      Ortalama 2 = CALCULATE(SUM(Liste[Karşılaştırma - 1]);ALLEXCEPT(Liste;Liste[Ödeme Kategorisi];AyTablo2[YA]))/DISTINCTCOUNT('AyTablo2'[YA])