Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there
I can't seem to get this to work - hoping you can help!
I have the following columns in a large table extracted from print logs:
DateGenerated Time Hour Environment
1.2.2016 10:14 10 LEGACY
1.2.2016 11:15 11 NEW
The columns have much more information, but essentially I just want to count the number of times "LEGACY" & "NEW" appear on a certain date. Ideally, I would also be able to count date & hour - but I don't want to be greedy!
This is all to produce a % of total for each day.
Hope you can help! Thanks
Solved! Go to Solution.
Here's the example I created
The result looks like this
Here are the Measures I used
LEGACY # = COUNTROWS(FILTER(Table1, Table1[Environment]="LEGACY")) LEGACY % = DIVIDE([LEGACY #], [TOTAL #], 0) NEW # = COUNTROWS(FILTER(Table1, Table1[Environment]="NEW")) NEW % = DIVIDE([NEW #],[TOTAL #],0) TOTAL # = [LEGACY #] + [NEW #]
Hope this helps!
Hello everyone,
I'm a bit confused here.
Doesn't the below calculated measure give the same results regardless of how many environment types you have? That way if you have more than two environments, the percentage will be calculated using only one query rather than one or two for each type.
Environment % =
DIVIDE(COUNTROWS(Table) ,
CALCULATE(
COUNTROWS(Table),
ALL(Table[Environment])
))
You then create a matrix where you put your date as rows, environment as columns, environment summarized as count and the calculated environment % as values.
Does it also work?
Here's the example I created
The result looks like this
Here are the Measures I used
LEGACY # = COUNTROWS(FILTER(Table1, Table1[Environment]="LEGACY")) LEGACY % = DIVIDE([LEGACY #], [TOTAL #], 0) NEW # = COUNTROWS(FILTER(Table1, Table1[Environment]="NEW")) NEW % = DIVIDE([NEW #],[TOTAL #],0) TOTAL # = [LEGACY #] + [NEW #]
Hope this helps!
That's great Sean - thanks so much!
Quick question - just wondering if you manually created the entries in the 'DateGenerated' & 'Hour' columns, or were these automatically populated?
Thanks
Bryan
@bryan_d DateGenerated and Time yes manually I just created a small table to show you the result - Hour derived from time
Anyway the problem with the above formula suggested although it works in a Matrix - it has more limited use
How are you going to display just the Legacy # with a Card? (not multi-row card)
Can you create a Line and Clusted Column Chart?
Sorry, yes distinct count only works if you have a unique row identifer. Your countrows formula are the best for your data.
Hi @bryan_d,
you can use the COUNTA() function.
I'm sorry @MattAllington I tried with the DISTINCTCOUNT() function, but I simply got 2 referred to the two different environments (as you can see from the screenshot).
Let Me Know If It Works.
#I'M Not An Expert#
Put dateGenerated in rows in a matrix (or on a chart axis or what ever). Then write
=DISTINCTCOUNT(yourTableName[Environment])
same applies for the others
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |