Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX Measure Count, group by, Filter.

Hello everyone,

 

I'm trying to write a meaure to Count the number of Puchase Contract if the Total Contract Consumptoin% exceeds 80% of the contract value.

Note: The Puchase Contract could have multiple items but i need the measure to count the Puchase Contract and apply the condition on the Contract Consumptoin% of total items per Puchase Contract (the percentage of the summed items).

 

Table= Contract

 

 

I would really apprecaite your support.

Thanks in advance.

3 Replies

  • JoeBarry's avatar
    JoeBarry
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Anonymous 

     

    You can try this measure

     

    Exceed 80% = CALCULATE(COUNT(Contract[Purchase Contract]), KEEPFILTERS([Contract Consumption%]>= 0.8)

     Thanks

    Joe

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks alot Joe.

       

      Exceed 80% = CALCULATE(COUNT(Contract[Purchase Contract]), FILTER(Contract,[Contract Consumption]>= 0.8))
       
      I've tried the above measure but it will calculate the count on line item level.  The Puchase Contracts have multiple items so i need the logic of the measure to calculate (Count) for the summed values for each Puchase Contract. (by grouping by Puchase Contract if possible)
       
      Please let me know if you need more illustration.
      Thanks again for your support
  • JoeBarry's avatar
    JoeBarry
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Anonymous 

    Can you show me in a screenshot your expected outcome? Maybe create a table within your comment. And maybe if the data isn't sensitive a screenshot of a few lines of the table?

    You can also try below. Presuming there is a Value in each Row, this will count all rows, therefore checking each consumption is over 80% 

     

    Exceed 80% = CALCULATE(COUNTROWS(Contract]), KEEPFILTERS([Contract Consumption%]>= 0.8)

     

     

    If you remove the Released Value from the Rows also after creating the measure from your Matrix, it should show you what you need.