Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GOEST
Frequent Visitor

COUNT and SUM in one column depending to another column

Hi everyone!

 

I am looking for a solution to the following problem for PowerBI Desktop: I would like to count all column values to a date in a column (HOURS) depending on a different column (TYPE, "z"). Then the count of the values which are greater than 10 is to be counted. The main problem for me is that I could count the hours, but I have not been able to sum up the number of hours over 10 (COUNTA ?)

 

Thanks

Stefan

 

Example:

 

NAME        DATE                 HOURS         TYPE
a                02.05.2016        3                   x
b                02.05.2016        5                   y
a                02.05.2016        6,5                x
b                02.05.2016        7                   x
a                02.05.2016        4                   z
a                03.05.2016        4                   x
b                03.05.2016        5                   x
b                03.05.2016        5,5                y
a                04.05.2016        6,5                x
a                04.05.2016        5                   x
b                04.05.2016        3,5                y
b                04.05.2016        6                   x
a                05.05.2016        6                   x
a                05.05.2016        5                   y
b                05.05.2016        7                   x
b                05.05.2016        5                   z

1 ACCEPTED SOLUTION

@GOEST

 

You can try to filter the “TotalHours_Measure” in Visual level filters as below. Hope it is what you desired.

 

COUNT and SUM in one column depending to another column_1.jpg 

 

Best Regards,

Herbert

View solution in original post

12 REPLIES 12
Stachu
Community Champion
Community Champion

hi @GOEST

I'm not sure I understand your problem fully - based on the data you attached, how would look your desired result?

It seems to me that it would be single number - how many TYPE entries have more than 10h, is that correct?

 

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

GOEST
Frequent Visitor

Hi @Stachu

Yes exactly - After the values were added per day. Only the values of TYPE "z" are not to be summed.

Stachu
Community Champion
Community Champion

can you try the following:

CountHours:=COUNTROWS(FILTER(SUMMARIZE('Table',[TYPE],"tot_hours",SUM('Table'[HOURS])),[tot_hours]>10))

 

replace 'Table' to your naming



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

GOEST
Frequent Visitor

Hi @Stachu

 

Thanks al lot for your answer!

 

It is not quite the desired result (see appendix).

Only the yello data should be summed and only green data should be counted. In this case the result should be 4.

 

PBITEST.jpg

 

 

 

@GOEST

 

Do you want to not calculate the count of type z even when its total hours are above 10? If that’s the case, you can try with following measure and put it into a card visual.

 

TotalHours_Measure = 
    CALCULATE (
        SUM ( Table1[HOURS] ),
        ALLEXCEPT ( Table1, Table1[NAME], Table1[DATE] ),
        Table1[TYPE] <> "z"
    )
Count = 
    COUNTROWS (
        FILTER (
            SUMMARIZECOLUMNS (
                Table1[NAME],
                Table1[DATE],
                "TotalHours", [TotalHours_Measure]
            ),
            [TotalHours] > 10
        )
)

COUNT and SUM in one column depending to another column_1.jpg

 

Best Regards,

Herbert

Hi @v-haibl-msft

 

Thank a lot!! It looks almost great. Do you know it it is possible to set visual or site filters by name or another criteria,  so I have only for these criteria the number of hours greater than 10?

 

Best regards

Stefan

@GOEST

 

You can try to filter the “TotalHours_Measure” in Visual level filters as below. Hope it is what you desired.

 

COUNT and SUM in one column depending to another column_1.jpg 

 

Best Regards,

Herbert

Hi @v-haibl-msft!

 

It works fantastic. There was still a stupid mistake, which I had installed. Thank you, your help was really great and I learned again a little more in the PowerBI.

 

Best regards

Stefan

GOEST
Frequent Visitor

Hi Herbert!

 

I have a little addition to my problem. We have a new column with the departments and we want to shown the number of hours over 10h depending on the departments and the time.

 

In this example I only could show the hours.

pbi_5.jpg

 

Is it possible to do this with the count measure?

 

Best regards

Stefan

@GOEST

 

Please try to replace Table1[NAME] with Table1[DEP] in measures.

 

TotalHours_Measure = 
    CALCULATE (
        SUM ( Table1[HOURS] ),
        ALLEXCEPT ( Table1, Table1[DEP], Table1[DATE] ),
        Table1[TYPE] <> "z"
)
Count = 
    COUNTROWS (
        FILTER (
            SUMMARIZECOLUMNS (
                Table1[DEP],
                Table1[DATE],
                "TotalHours", [TotalHours_Measure]
            ),
            [TotalHours] > 10
        )
)

 

Best Regards,

Herbert

Hi Herbert!

That was my first attempt. But then I do not manage to represent the number of hours over 10 (counts) depending on the DEP and the time:

 

pbi_6.jpgpbi_6a.jpgpbi_6b.jpg

 

Best regards

Stefan

GOEST
Frequent Visitor

Hi!

Could this be a possible solution?

 

Could I generate with DAX a new related table with the values for COUNT, DEP, DATE? Then I could visualize the amount of hours over 10 in relation to DEP and Time.

Do you have a tip how I could generate this table with DAX?

 

Best regards

Stefan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.