Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bayezidalarabi
Frequent Visitor

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

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 idCatActivitygroupmonthMeasureValuesellcost
1LS1AM1A01/01/2020Budget10001110
1LS1AM1A01/01/2020Forecast10001110
1LS1BM1B01/01/2020Budget30001110
1LS1BM1B01/01/2020Forecast30001110
2LS1AM1A01/01/2020Budget10002215
2LS1AM1A01/01/2020Forecast10002215
2LS1BM1B01/01/2020Budget30002215
2LS1BM1B01/01/2020Forecast30002215
3CD3AM2A01/01/2020Budget40003325
3CD3AM2A01/01/2020Forecast03325
3CD3BM2B01/01/2020Budget20003325
3CD3BM2B01/01/2020Forecast03325

 

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.

 

1 ACCEPTED SOLUTION
bayezidalarabi
Frequent Visitor

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

View solution in original post

3 REPLIES 3
bayezidalarabi
Frequent Visitor

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
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.