Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to get the following dataset to output the number of ID#'s that meet a set of Type critera ... Ideally I would like to be able to toggle how many ID#s purchased "Theatre and Opera" or "Business AND Theatre"
ID# | Type | Amount |
2 | Theatre | $50 |
14 | Theatre | $50 |
14 | Business | $10000 |
2 | Opera | $65 |
14 | Opera | $95 |
Desired Outcome:
Slicer: Theatre AND Opera
Distinct Count ID#= 2
Total Spend#= $260
Slicer: Theatre AND Business
Distinct Count ID#= 1
Total Spend#= $10050
It is important that people are able to filter by the Types of their choosing as it will be used for multiple departments. Any ideas?
Solved! Go to Solution.
Hi @chq
Try the following measures:
Count IDs =
VAR SelTypes = VALUES( Table1[Type] )
VAR CountSelTypes = COUNTROWS( SelTypes )
VAR Result =
COUNTROWS(
FILTER(
ADDCOLUMNS(
VALUES( Table1[ID#] ),
"@TypeCount", CALCULATE( DISTINCTCOUNT( Table1[Type] ), VALUES( Table1[Type] ) )
),
[@TypeCount] = CountSelTypes
)
)
RETURN Result
Total Value =
VAR SelTypes = VALUES( Table1[Type] )
VAR CountSelTypes = COUNTROWS( SelTypes )
VAR Result =
SUMX(
FILTER(
ADDCOLUMNS(
VALUES( Table1[ID#] ),
"@TypeCount", CALCULATE( DISTINCTCOUNT( Table1[Type] ), VALUES( Table1[Type] ) ),
"@Value", CALCULATE( SUM( Table1[Amount] ) )
),
[@TypeCount] = CountSelTypes
),
[@Value]
)
RETURN Result
Best regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
Here is an alternative solution:
The model:
1) calculate the ditinctcount of types in the data table:
Distinctcount Types = DISTINCTCOUNT('DataTable'[Type])
2) calculate the Distinctcount based on the slicer selection (AND condition):
Distinctcount =
VAR Ids = COUNTROWS('Type Slicer')
RETURN
CALCULATE(DISTINCTCOUNT('DataTable'[ID#]);
FILTER(ALLSELECTED('DataTable'[ID#]);
Ids = [Distinctcount Types]))
3) Calculate the Amount based on the slicer selection (condition AND);
Amount for selected Types = CALCULATE([Sum of Amount];
FILTER('ID Slicer';
[Distinctcount] >0))
And you get this (The "ID" filed in the table visual is from the "ID Slicer" table):
and here is the PBIX file if you're interested:
Proud to be a Super User!
Paul on Linkedin.
Here is an alternative solution:
The model:
1) calculate the ditinctcount of types in the data table:
Distinctcount Types = DISTINCTCOUNT('DataTable'[Type])
2) calculate the Distinctcount based on the slicer selection (AND condition):
Distinctcount =
VAR Ids = COUNTROWS('Type Slicer')
RETURN
CALCULATE(DISTINCTCOUNT('DataTable'[ID#]);
FILTER(ALLSELECTED('DataTable'[ID#]);
Ids = [Distinctcount Types]))
3) Calculate the Amount based on the slicer selection (condition AND);
Amount for selected Types = CALCULATE([Sum of Amount];
FILTER('ID Slicer';
[Distinctcount] >0))
And you get this (The "ID" filed in the table visual is from the "ID Slicer" table):
and here is the PBIX file if you're interested:
Proud to be a Super User!
Paul on Linkedin.
Hi @chq
Try the following measures:
Count IDs =
VAR SelTypes = VALUES( Table1[Type] )
VAR CountSelTypes = COUNTROWS( SelTypes )
VAR Result =
COUNTROWS(
FILTER(
ADDCOLUMNS(
VALUES( Table1[ID#] ),
"@TypeCount", CALCULATE( DISTINCTCOUNT( Table1[Type] ), VALUES( Table1[Type] ) )
),
[@TypeCount] = CountSelTypes
)
)
RETURN Result
Total Value =
VAR SelTypes = VALUES( Table1[Type] )
VAR CountSelTypes = COUNTROWS( SelTypes )
VAR Result =
SUMX(
FILTER(
ADDCOLUMNS(
VALUES( Table1[ID#] ),
"@TypeCount", CALCULATE( DISTINCTCOUNT( Table1[Type] ), VALUES( Table1[Type] ) ),
"@Value", CALCULATE( SUM( Table1[Amount] ) )
),
[@TypeCount] = CountSelTypes
),
[@Value]
)
RETURN Result
Best regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
@chq something is not clear in your output, in case of 2nd choice, Theatre and Business, isn't the distinct ID count will be 2 instead of 1?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
No as only ID# 14 will meet the AND requirements.
@chq so based on Ids are common in the selected Type , dont think it means distinct
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |