Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I need to summarize the data below (part of the schema) which has repeated values:
I am having problem in summarizing the data on following scenario:
Scenario 1.
Sum of "sell" for all "activity id" - should consider - max "sell" and sum
My work around:
Didn't give me expected result, it is showing in tabular format without calculation correct data whereas in graph it is adding up all - there is no way i can exclude calculation:
CALCULATE(
MAX('Table'[cost]),
ALLEXCEPT('Table','Table'[activity id])
Scenario 2.
Similar to scenario 1, I need sum of Value based on Cat, Activity, Group, Month - sum of max
My work around:
Tried following, worrking fine in tabular format without calculation but not in graph as it needs sum/avg etc.
CALCULATE
(
MAX('Table'[Value]), ALLEXCEPT('Table','Table'[Cat],'Table'[Activity], 'Table'[group], 'Table'[month])
)
activity id | Cat | Activity | group | month | Measure | Value | sell | cost |
1 | LS | 1A | M1A | 01/01/2020 | Budget | 1000 | 11 | 10 |
1 | LS | 1A | M1A | 01/01/2020 | Forecast | 1000 | 11 | 10 |
1 | LS | 1B | M1B | 01/01/2020 | Budget | 3000 | 11 | 10 |
1 | LS | 1B | M1B | 01/01/2020 | Forecast | 3000 | 11 | 10 |
2 | LS | 1A | M1A | 01/01/2020 | Budget | 1000 | 22 | 15 |
2 | LS | 1A | M1A | 01/01/2020 | Forecast | 1000 | 22 | 15 |
2 | LS | 1B | M1B | 01/01/2020 | Budget | 3000 | 22 | 15 |
2 | LS | 1B | M1B | 01/01/2020 | Forecast | 3000 | 22 | 15 |
3 | CD | 3A | M2A | 01/01/2020 | Budget | 4000 | 33 | 25 |
3 | CD | 3A | M2A | 01/01/2020 | Forecast | 0 | 33 | 25 |
3 | CD | 3B | M2B | 01/01/2020 | Budget | 2000 | 33 | 25 |
3 | CD | 3B | M2B | 01/01/2020 | Forecast | 0 | 33 | 25 |
Background:
Migrating from Tableau to Power BI:
Used LOD functin in Tableau:
{ INCLUDE [activity id]:max([sell])}
{ INCLUDE [activity], [Cat], [group], [month]: max([Value])}
Please share your ideas/experience solving the similar issue.
Solved! Go to Solution.
Scenario 2:
Start from lowest hierarchy (Activity=>Cat=>Group=>Month) and repeat the SUMX as below measures:
_____________________________________________________
Total Budget Activity =
SUMX
(
VALUES('Program Mngmnt'[activity]),
CALCULATE
(
MAX('Program Mngmnt'[Value])
)
)
_____________________________________________________
Total Budget Category =
SUMX
(
VALUES('Program Mngmnt'[Category]),
CALCULATE
(
[Total Budget Activity]
)
)
_____________________________________________________
Total Budget Group =
SUMX
(
VALUES('Program Mngmnt'[group]),
CALCULATE
(
[Total Budget Category]
)
)
_____________________________________________________
Total Budget =
SUMX
(
VALUES('Program Mngmnt'[month]),
CALCULATE
(
[Total Budget Group]
)
)
_____________________________________________________
Followed similar approach for Scenario 1.
Thanks everyone for your support
Scenario 2:
Start from lowest hierarchy (Activity=>Cat=>Group=>Month) and repeat the SUMX as below measures:
_____________________________________________________
Total Budget Activity =
SUMX
(
VALUES('Program Mngmnt'[activity]),
CALCULATE
(
MAX('Program Mngmnt'[Value])
)
)
_____________________________________________________
Total Budget Category =
SUMX
(
VALUES('Program Mngmnt'[Category]),
CALCULATE
(
[Total Budget Activity]
)
)
_____________________________________________________
Total Budget Group =
SUMX
(
VALUES('Program Mngmnt'[group]),
CALCULATE
(
[Total Budget Category]
)
)
_____________________________________________________
Total Budget =
SUMX
(
VALUES('Program Mngmnt'[month]),
CALCULATE
(
[Total Budget Group]
)
)
_____________________________________________________
Followed similar approach for Scenario 1.
Thanks everyone for your support
Hey @bayezidalarabi ,
on a 2nd thought, I guess the measure for the 1st scenario is something like this:
SUMX(
VALUES('<table>'[activity id]) //the table to iterate
, MAX('<table>'[activity id]) //the numeric expression
)
Hopefully, this provides an additional idea.
Regards,
Tom
Hey @bayezidalarabi ,
I have to admit, that I do not understand your requirements.
Cany you please provide some scribbles of the visualizations that are working, and that are not working (please describe what is not working).
Regards,
Tom
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |