Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
You can try to filter the “TotalHours_Measure” in Visual level filters as below. Hope it is what you desired.
Best Regards,
Herbert
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?
Hi @Stachu
Yes exactly - After the values were added per day. Only the values of TYPE "z" are not to be summed.
can you try the following:
CountHours:=COUNTROWS(FILTER(SUMMARIZE('Table',[TYPE],"tot_hours",SUM('Table'[HOURS])),[tot_hours]>10))
replace 'Table' to your naming
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.
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 ) )
Best Regards,
Herbert
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
You can try to filter the “TotalHours_Measure” in Visual level filters as below. Hope it is what you desired.
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
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.
Is it possible to do this with the count measure?
Best regards
Stefan
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:
Best regards
Stefan
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |