Custom Summary based on selected columns - similar to LOD in Tableau and visualize in Power BI

[bayezidalarabi]

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.

[bayezidalarabi · Accepted 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

[TomMartens]

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

[TomMartens]

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