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.
I have the data in the following format and I need to find the sum of amount for each type and subtract the sum of amount for base from it.
Then, I have to make a histogram with the bins based on the outcome of this mathematical function and plot it against the number of occurrences on the y axis.
I don't know how to go about it. Kindly help.
Solved! Go to Solution.
Hi @Anonymous
I create a new table
Table 2 =
VAR Table3 =
SELECTCOLUMNS (
FILTER (
SUMMARIZE ( 'Table', 'Table'[type], "TOTAL AMOUNT", SUM ( 'Table'[amount] ) ),
[type] = "BASE"
),
"type2", [type],
"TOTAL AMOUNT2", [TOTAL AMOUNT]
)
RETURN
CROSSJOIN (
FILTER (
SUMMARIZE (
'Table',
'Table'[type],
"TOTAL AMOUNT", SUM ( 'Table'[amount] ),
"count", COUNT ( 'Table'[type] )
),
[type] <> "BASE"
),
Table3
)
then add columns in the column chart.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Anonymous
I can get the result of the following:
"the sum of amount for each type and subtract the sum of amount for base"
But I'm not clear how to get the "average count".
The result above is from a calculated table, if you can accept this method, please let me know how to create the "average count".
Also, can you please post the entire DAX expression for the table?
Hi @Anonymous
I create a new table
Table 2 =
VAR Table3 =
SELECTCOLUMNS (
FILTER (
SUMMARIZE ( 'Table', 'Table'[type], "TOTAL AMOUNT", SUM ( 'Table'[amount] ) ),
[type] = "BASE"
),
"type2", [type],
"TOTAL AMOUNT2", [TOTAL AMOUNT]
)
RETURN
CROSSJOIN (
FILTER (
SUMMARIZE (
'Table',
'Table'[type],
"TOTAL AMOUNT", SUM ( 'Table'[amount] ),
"count", COUNT ( 'Table'[type] )
),
[type] <> "BASE"
),
Table3
)
then add columns in the column chart.
Hi Maggie,
Ignore the table 'average table'. I had made it just to point out the count in each bin.
I wanted to know how do i make these bins in the histogram and display the data.
Now that we have got the result of the subtraction in the "Column", How do i display they count on the histogram and make those bins?
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |