Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Sum Breakdown

Hi and good day, can anyone help me on how can i get the result we required. I have a table and base from Column "CountMAX/Week/Vessel" I want the breakdown same as this 

CAT BP'sLocationTrade_to_USEDWeek No:CountMAX/Week/VesselDirect/InDirect
MechSouthTrade013432Direct
CivilSouthTrade013432Direct
ElecSouthTrade013432InDirect
ElecSouthTrade013432Direct
CivilSouthTrade013432InDirect
MechSouthTrade013432Direct
ElecSouthTrade013432Direct
ElecSouthTrade013432InDirect
CivilSouthTrade013432Direct
ElecSouthTrade023432Direct
CivilSouthTrade023432Direct
ElecSouthTrade023432InDirect
ElecSouthTrade023432Direct
MechSouthTrade023432Direct
CivilSouthTrade033432InDirect
CivilNorthTrade033432Direct
ElecNorthTrade033432Direct
MechNorthTrade033432InDirect
ElecNorthTrade033432Direct
CivilNorthTrade033432Direct
MechNorthTrade033432Direct
ElecNorthTrade043432InDirect
ElecNorthTrade043432Direct
MechNorthTrade043432InDirect
ElecNorthTrade043432Direct
CivilNorthTrade043432Direct
MechNorthTrade043432Direct
ElecNorthTrade043432InDirect
ElecNorthTrade053432Direct
MechNorthTrade053432Direct
CivilNorthTrade053432InDirect
ElecNorthTrade053432Direct
ElecNorthTrade053524InDirect
ElecNorthTrade053524Direct
ElecNorthTrade063524InDirect
MechNorthTrade063524Direct
ElecNorthTrade063524Direct
ElecNorthTrade063524InDirect
MechNorthTrade063524Direct
CivilNorthTrade063524InDirect
ElecNorthTrade063524Direct
MechNorthTrade063524InDirect
ElecNorthTrade073524InDirect
CivilSouthTrade073524Direct
MechSouthTrade073524InDirect
ElecSouthTrade073524Direct
CivilSouthTrade073524InDirect
MechSouthTrade073524Direct
ElecSouthTrade073524Direct
ElecSouthTrade073524Direct
MechSouthTrade073524InDirect
ElecSouthTrade073524Direct
CivilSouthTrade073524InDirect
MechSouthTrade073524Direct
ElecSouthTrade073524InDirect
CivilSouthTrade073524Direct

 

Need Help

12 Replies

  • AllanBerces's avatar
    AllanBerces
    Post Prodigy
    1 year ago

    CountMAX/Week/Vessel is come from my calculated column

    CountMAX/Week/Vessel =
    VAR _currentWeek = 'Table01'[Week No:]
    VAR _currentCode = 'Table01'[Location]
    RETURN
        CALCULATE (
            MAX ( 'Table01'[Count/Trade/Vessel] ),
            FILTER (
                ALL ( 'Table01' ),
                'Table01'[Week No:] = _currentWeek &&
                'Table01'[Used_Vessel Code] = _currentCode
            )
        )
  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    Hi AllanBerces ,

    Create two measures (one for Direct, one for Indirect) that sum CountMAX/Week/Vessel filtered by the Direct/InDirect flag.


    Build a matrix with Week No, Location, Trade_to_USED as rows. Put Direct Count and Indirect Count as values (two columns).


    DAX (adjust to your actual table/column names)

    Direct count Direct Count = CALCULATE( SUM( 'YourTable'[CountMAX/Week/Vessel] ), 'YourTable'[Direct/InDirect] = "Direct" )

     

    Indirect count Indirect Count = CALCULATE( SUM( 'YourTable'[CountMAX/Week/Vessel] ), 'YourTable'[Direct/InDirect] = "InDirect" )

     

    Optional: a Grand Total measure (not strictly needed if you just want the sum) Grand Total = [Direct Count] + [Indirect Count]

     

    Building the matrix

    Rows: Week No, Location, Trade_to_USED (in that order for the same nesting as your sample)
    Columns: Direct Count (first column) and Indirect Count (second column)
    Values: Direct Count, Indirect Count (you can remove the Grand Total measure from the matrix since the matrix will show sums, and you can enable the total if you want)

     

    Please mark this post as solution if it helps you. Appreciate Kudos.

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi FarhanJeelani thank you for the reply but the result is not correct, on my table i have a date column let say Week 34 from 18 - 24 of Aug and another column the value on each day let say Aug 18=23 Aug 19=37 and so on . and on my column CountMAX/Week/Vessel will register the highest number Let say Aug 19 is the highest then 37 will register on my column CountMAX/Week/Vessel and that 37 i want to breakdown.

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    1 year ago

    Use Power BI 
    - Rows: Location, CAT BP's
    - Columns: Week No, Direct/InDirect
    - Values: Count of rows (or sum CountMAX/Week/Vessel if needed)


    ✅ Or use DAX:


    SummaryTable =
    SUMMARIZE(
    YourData,
    YourData[Week No],
    YourData[Location],
    YourData[CAT BP's],
    YourData[Direct/InDirect],
    "Count", COUNTROWS(YourData)
    )


    This gives you the exact breakdown by week, location, category, and Direct/InDirect.

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi Shahid12523 thank you for the reply but it not givin the right value. it summarize all the data in a week, what we required is the breakdown of the highest value on the particular day of the week.

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi something like this on one location. but i have multiple location

       

       

       

  • danextian's avatar
    danextian
    Super User
    1 year ago

    How did you come up with the numbers in your expected result? Counting the rows don't match. Please use a sample data that actually represents the data in your semantic model.

     

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi danextian thank you for the reply, my mistake on that can use below and result we required

      Thank you

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    11 months ago

    Hi AllanBerces ,

    Can you please provide correct sample data in a form of table or a file and not in screenshot?
    This will help us understand your data better and it would be easier to provide you proper solution.
    Thank You

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    11 months ago

    Hi AllanBerces ,
    Since we didnt hear back, we would be closing this thread.
    If you need any assistance, feel free to reach out by creating a new post.

    Thank you for using Microsoft Community Forum