Forum Discussion

rjiang's avatar
rjiang
Helper I
5 years ago
Solved

Matrix totals

I have a measure [Clicks in Last 3 Months] which is displays the number of clicks in the last 3 months, which is calculating as expected (shown below)


I would like to have a measure that evaluates to 1 if [Clicks in Last 3 Months] is greater than or equal to 3, and evaluate to 0 otherwise.

The measure I used to evaluate this is:

 

[Active User] = IF([Clicks in Last 3 Months]>=3,1,0)

 

,which gives me the matrix shown below

 

 

I'd like the company totals (highlighted yellow) to correctly display the sum of the rows. I believe I'm supposed to use a SUMX or COUNTX here, but I am unsure how to proceed since I want to filter based on a measure output instead of filtering a column.

 

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi rjiang ,

     

    Based on your description, I created a test pbix for your reference.

    Try to create measure like below:

    Measure 3 = COUNTROWS(
    FILTER(
    SUMMARIZE(financials,
    financials[Country],
    financials[Product],
    "COUNT_",
    COUNT(financials[Product])),
[COUNT_]>1))+0

     

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

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    rjiang try this:

     

    [Active User] = SUMX ( VALUES ( YourTable[ColumnOnRows] ), IF([Clicks in Last 3 Months]>=3,1,0) )

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • rjiang's avatar
      rjiang
      Helper I
      5 years ago

      When I use 

      [Active User] = SUMX ( VALUES ( YourTable[ColumnOnRows] ), IF([Clicks in Last 3 Months]>=3,1,0) )

       

      I get:

       

       

       

      Which doesnt correspond to the clicks shown below

       

       

      Perhaps there's an error with the way I calculate the with [Clicks in Last 3 Months]?

       

      [Clicks in Last 3 Months] =

      CALCULATE(
      SUMX('Usage Data',[Clicks]),
      DATESINPERIOD('Date Lookup'[Date],MAX('Date Lookup'[Date]),-4,MONTH)
      )-
      CALCULATE(
      SUMX('Usage Data',[Clicks]),
      DATESINPERIOD('Date Lookup'[Date],MAX('Date Lookup'[Date]),-1,MONTH)
      )
  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    Hi rjiang ,

     

    Based on your description, I created a test pbix for your reference.

    Try to create measure like below:

    Measure 3 = COUNTROWS(
    FILTER(
    SUMMARIZE(financials,
    financials[Country],
    financials[Product],
    "COUNT_",
    COUNT(financials[Product])),
[COUNT_]>1))+0

     

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