The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I need help with counting the number of times a measure appears in multiple columns.
In the image above you can see that for the Client 33, with various users, the most common number for MOD19 (19 Days in a Month) is 8,43.
What I want to know is how can I count the number of times 8,43 appears in MOD19, MOD 20, MOD21 and MOD 22.
And then 8,01 in MOD19, MOD 20, MOD21 and MOD 22.
7,63 in.... ETC
The answer should be something like this p.e.:
no_PHC | MOD19 | MOD20 | MOD21 | MOD22 | C_MOD19 | C_MOD20 | C_MOD21 | C_MOD22 |
33 | 8,43 | 8,01 | 7,63 | 7,28 | 16 | 16 | 24 | 16 |
File PBI:
Here's the excel file that i'm trying to reproduce in PBI.
File Xlsx (Right values):
If it is easier in excel, i'll accept it. 😀
Hi @Anonymous ,
It is suggested to UnPivot the 4 "cen" columns like below:
Then create measures like so:
MOD =
MAXX (
TOPN (
1,
ADDCOLUMNS (
VALUES ( 'Face Value Test'[Value] ),
"Moda", CALCULATE ( COUNT ( 'Face Value Test'[Value] ) )
),
[Moda], 0
),
'Face Value Test'[Value]
)
Count_MOD_3 =
VAR MOD_ =
CALCULATE ( [MOD], ALL ( 'Face Value Test'[Value] ) )
VAR MaxMOD_ =
IF ( MAX ( 'Face Value Test'[Value] ) = MOD_, MOD_ )
RETURN
CALCULATE (
COUNT ( 'Face Value Test'[Value] ),
'Face Value Test'[Value] = MaxMOD_
)
Filter Measure =
IF (
MAX ( 'Face Value Test'[Value] )
= CALCULATE ( [MOD], ALL ( 'Face Value Test'[Value] ) ),
1
)
And you will get this:
Please check if the attachment is what you want.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much for your help. But it not correct.
I know what the right values are.
I'll add the excel file that does what i want to do in PBI.
@Anonymous
Please share a sample or dummy PBI file with this information.
Save it in One Drive or Google Drive and share the link
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
21 | |
14 | |
14 | |
9 | |
7 |