Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

PowerBI Matrix question

Hi guys,

 

Would you please guide me how to avhieve the following?

I have a matrix displayed with quartery values:

Quarterly = CONCATENATE("Q", ROUNDUP(MONTH([Date]) / 3 ,0))
 
Names     Q1     Q2      Q3      Q4      Total
Name1      1                  1          1            3
Name2                           3         1            4
Name3      4        2         5                      11
 
And I would like to to display 1 instead of actual values and get a total of those ones.
 
Names     Q1     Q2      Q3      Q4      Total
Name1      1                  1          1            3
Name2                           1         1            2
Name3      1        1         1                       3
 
But, I am getting something like this
 
Names     Q1     Q2      Q3      Q4      Total
Name1      1                  1          1            1
Name2                           1         1            1
Name3      1        1         1                       1
 
I am using the following formula calculation:
 
Test =
VAR CurrentDate = MAX('Date'[Date])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) -12, DAY(CurrentDate) + 1)
VAR ValDisplayed =
CALCULATE(
[Count Distinct ID],
FILTER('Calendar','Calendar'[Date] >= PreviousDate && 'Calendar'[Date] <= CurrentDate))
RETURN
IF(ValDisplayed >= 1, 1)
 
Thank you very much in advance.
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi all,

     

    I took some time to full understand amitchandak first approach.

    And understanding how Matrix Sumx() function works.

    https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=5c7a798d-9143-4395-9a59-35f92fece5e0&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

     

    I was able to use amitchandak first approach to solve my issue, and it goes like this:

     

    Test =
    VAR CurrentDate = MAX('Date'[Date])
    VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) -12, DAY(CurrentDate) + 1)
    VAR ValDisplayed =
    CALCULATE(
    [Count Interactions Number],
    FILTER('Calendar','Calendar'[Date] >= PreviousDate && 'Calendar'[Date] <= CurrentDate))
    VAR CombineTable = ADDCOLUMNS('Business', "Businesses", [Count Businesses Number], "Names", RELATED('People'[Person Name]) , "Quartery", RELATED('Calendar'[Quarterly]))
    RETURN
    SUMX(SUMMARIZE(CombineTable, [Businesses], [Name], [Quartery]), IF(ValDisplayed >= 0 , 1))

     


    Names       Q1     Q2    Q3    Q4    Total
    Name          1                1       1       3
    Name                            1       1       2
    Name         1        1       1                3

     

    That's it guys and may thanks to amitchandak 

     

    Cheers!

     

    S.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try measure in one of the two ways

     

    Test =
    VAR CurrentDate = MAX('Date'[Date])
    VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) -12, DAY(CurrentDate) + 1)
    VAR ValDisplayed =
    CALCULATE(
    [Count Distinct ID],
    FILTER('Calendar','Calendar'[Date] >= PreviousDate && 'Calendar'[Date] <= CurrentDate))
    RETURN
    sumx(summarize(Table, Table[Name], Date[Quarterly], "_1", IF(ValDisplayed >= 1, 1)),[_1])

     

    or


    Test =
    VAR CurrentDate = MAX('Date'[Date])
    VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) -12, DAY(CurrentDate) + 1)
    VAR ValDisplayed =
    CALCULATE(
    [Count Distinct ID],
    FILTER('Calendar','Calendar'[Date] >= PreviousDate && 'Calendar'[Date] <= CurrentDate))
    RETURN
    sumx(values(Date[Quarterly]), IF(ValDisplayed >= 1, 1))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amitchandak,

       

      Thank you for a quick reply. I am trying to get back to you for feedback. And clarify a little about my model.

       

      "Calender" ---> "Calender[Calender Quaterly]" is connected to "Business" ---> "Business[Distinct Count Business Number]"

      "People" ----> "People[Person Name]" is connected to "Business"

      "Date" is not connected to anything, it is simply used to draw out a yearly period data.

       

      The first approach did not work, because SUMX will work on one table, andnd I tried the following approach with no chance yet.

      https://community.powerbi.com/t5/Desktop/Summarize-multiple-tables-in-DAX/m-p/642693 

       

      The second of the methods you proposed, produced 1 values and "25" as Total for all the rows. I am not quite sure how to make this one work.

       

      Thank you very much for looking at this inquiry. Much appraciated.

       

      Steve

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi all,

         

        I took some time to full understand amitchandak first approach.

        And understanding how Matrix Sumx() function works.

        https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=5c7a798d-9143-4395-9a59-35f92fece5e0&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

         

        I was able to use amitchandak first approach to solve my issue, and it goes like this:

         

        Test =
        VAR CurrentDate = MAX('Date'[Date])
        VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) -12, DAY(CurrentDate) + 1)
        VAR ValDisplayed =
        CALCULATE(
        [Count Interactions Number],
        FILTER('Calendar','Calendar'[Date] >= PreviousDate && 'Calendar'[Date] <= CurrentDate))
        VAR CombineTable = ADDCOLUMNS('Business', "Businesses", [Count Businesses Number], "Names", RELATED('People'[Person Name]) , "Quartery", RELATED('Calendar'[Quarterly]))
        RETURN
        SUMX(SUMMARIZE(CombineTable, [Businesses], [Name], [Quartery]), IF(ValDisplayed >= 0 , 1))

         


        Names       Q1     Q2    Q3    Q4    Total
        Name          1                1       1       3
        Name                            1       1       2
        Name         1        1       1                3

         

        That's it guys and may thanks to amitchandak 

         

        Cheers!

         

        S.