- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sum with specific filter
Hi all,
I would like to ask you for help with following situation. I have this kind of data:
And I need to activate filter for code = 20 and see how many hours was reported for each operation. But when I activate this filter it looks like this:
So I need to get somehow the hours from specific PO and specific OPERATION and CODE 01 to CODE 20. It will looks like this:
PO - OPER - CODE - HOURS - QUANTITY
PO - 0901 - 20 - 0,67 - 1,00
PO - 0902 - 20 - 0,69 - 1,00
PO - 0903 - 20 - 0,59 - 1,00
PO - 0904 - 20 - 0,83 - 1,00
PO - 0905 - 20 - 1,42 - 1,00
PO - 0990 - 20 - 1,00 - 1,00
Thank you in advance.
P.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I cannot check whether my measure is working as expected without seeing a different PO number in the data.
Please check the attached pbix file, that I have fixed a little bit. Or, provide your sample pbix file's link in order to see the correct structure of your data.
Thanks.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I assume the logic is getting accumulate total in each operation.
Expected measure hours: =
IF (
COUNTROWS ( ALLSELECTED ( Data[Code] ) ) = COUNTROWS ( ALL ( Data[Code] ) ),
SUM ( Data[Hours] ),
CALCULATE (
SUM ( Data[Hours] ),
WINDOW (
1,
ABS,
0,
REL,
ALL ( Data[PO], Data[Operation], Data[Code] ),
ORDERBY ( Data[Code], ASC ),
KEEP,
PARTITIONBY ( Data[Operation] )
)
)
)
Expected measure quantity: =
IF (
COUNTROWS ( ALLSELECTED ( Data[Code] ) ) = COUNTROWS ( ALL ( Data[Code] ) ),
SUM ( Data[Quantity] ),
CALCULATE (
SUM ( Data[Quantity] ),
WINDOW (
1,
ABS,
0,
REL,
ALL ( Data[PO], Data[Operation], Data[Code] ),
ORDERBY ( Data[Code], ASC ),
KEEP,
PARTITIONBY ( Data[Operation] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Jihwan_Kim ,
thank you for your help. I forgot to mention that in the database is more than 1 PO so it needs to be filtered also by the PO. That´s the probably reason why I get this output:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I cannot check whether my measure is working as expected without seeing a different PO number in the data.
Please check the attached pbix file, that I have fixed a little bit. Or, provide your sample pbix file's link in order to see the correct structure of your data.
Thanks.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
10-29-2021 12:56 PM | |||
04-02-2024 12:05 AM | |||
11-29-2023 03:27 AM | |||
06-05-2024 12:18 PM | |||
07-22-2024 05:14 PM |
User | Count |
---|---|
107 | |
76 | |
53 | |
52 | |
43 |
User | Count |
---|---|
168 | |
114 | |
74 | |
61 | |
52 |