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.
Hi,
I have a data like in the table below:
On my chart I want to aggregate them like in the table below:
1. I want to show only values where the sum < 0
2. Divide the sum/total to present the result as a contribution
3. Be able to filter by other columns
Right now I have only DAX which helps me get the elements where the value is bigger than zero:
CALCULATE(SUM('Table'[Measure]),
FILTER(ALL('Table'[Measure]),
SUM('Table'[Measure]) < 0))
Could you help me with this, please?
Thank you in advance!
Solved! Go to Solution.
Hi @lokosrio
could you try to write your measures like this:
Total =
VAR _tmp =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( 'Table'[Dimension 1] ), ALL ( 'Table'[Dimension 1] ) ),
"t", CALCULATE ( SUM ( 'Table'[Amount] ) )
),
[t] < 0
),
'Table'[Dimension 1]
)
RETURN
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER ( ALL ( 'Table'[Dimension 1] ), 'Table'[Dimension 1] IN _tmp )
)
and
Contribution = divide(sum('Table'[Amount]),[Total])
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi @lokosrio ,
Try:
sum if<0 =
VAR sum_by_dim1 =
CALCULATE (
SUM ( 'Table (2)'[Measure] ),
ALLEXCEPT ( 'Table (2)', 'Table (2)'[Dimension1] )
)
RETURN
IF ( sum_by_dim1 < 0, sum_by_dim1 )
result2 =
VAR total =
CALCULATE (
SUM ( 'Table (2)'[Measure] ),
FILTER ( ALL ( 'Table (2)' ), [sum if<0] < 0 )
)
RETURN
DIVIDE ( [sum if<0], total )
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @lokosrio
could you try to write your measures like this:
Total =
VAR _tmp =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( 'Table'[Dimension 1] ), ALL ( 'Table'[Dimension 1] ) ),
"t", CALCULATE ( SUM ( 'Table'[Amount] ) )
),
[t] < 0
),
'Table'[Dimension 1]
)
RETURN
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER ( ALL ( 'Table'[Dimension 1] ), 'Table'[Dimension 1] IN _tmp )
)
and
Contribution = divide(sum('Table'[Amount]),[Total])
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi @sturlaws,
works perfect! Thank you very much.
How can I add a column ( 'Table'[Dimension 2] ) in your code to apply drill down on a chart?
Regards
When you drill down, the contribution should sum up to 100% on that selection?
Yes, the contribution should sum up to 100%
You would have to compute for the other dimension as well, and use ISFILTERED to determine which total to use:
Total =
VAR _tmpDimension1 =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( 'Table'[Dimension 1] ), ALL ( 'Table'[Dimension 1] ) ),
"t", CALCULATE ( SUM ( 'Table'[Amount] ) )
),
[t] < 0
),
'Table'[Dimension 1]
)
VAR _tmpDimension2 =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( 'Table'[Filter 2] ), ALL ( 'Table'[Filter 2] ) ),
"t", CALCULATE ( SUM ( 'Table'[Amount] ) )
),
[t] < 0
),
'Table'[Filter 2]
)
RETURN
IF (
ISFILTERED ( 'Table'[Filter 2] ),
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER ( ALL ( 'Table'[Filter 2] ), 'Table'[Filter 2] IN _tmpDimension2 )
),
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER ( ALL ( 'Table'[Dimension 1] ), 'Table'[Dimension 1] IN _tmpDimension1 )
)
)
@lokosrio , Try like
divide(if(SUM('Table'[Measure])<0,SUM('Table'[Measure]),blank()),calculate(SUM('Table'[Measure]), all('Table')))
Unfortunately the result is not ok:
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 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |