Forum Discussion

Alina12's avatar
Alina12
Helper I
2 years ago
Solved

Missing Grand Total Column in Matrix table

Hello to all

I have a problem with my Matrix Visual or probably the Measure I am using. After a long time I finally managed to get it to show me the correct values in the context. However, I am now missing the grand total column. The companies are in the header row and the story keys are in the header column.

--------------------------------------------------------------------------------------------------------------------

 

2Delivered Value =
VAR PricePerStory = SELECTEDVALUE('JiraExport_Sprints_SharepointFolder'[storyprice])
VAR CurrentSprint = SELECTEDVALUE('JiraExport_Sprints_SharepointFolder'[started in sprint])
VAR CurrentTeam = SELECTEDVALUE('JiraExport_Sprints_SharepointFolder'[TeamID])
VAR CurrentCompany = SELECTEDVALUE('Companies'[CompanyID])
VAR RollingTotalPercentage =
    CALCULATE(
        SUMX('Running Total', 'Running Total'[Rolling Total (%)]),
        FILTER(
            ALL('Running Total'),
            'Running Total'[Sprintnumber] = CurrentSprint &&
            'Running Total'[TeamID] =CurrentTeam &&
            'Running Total'[CompanyID] =CurrentCompany
        )
    )
RETURN
    PricePerStory * RollingTotalPercentage
-----------------------------------------------------------------------------------------------------------
Running Total (%) is a calculated column with the following formula:

Rolling Total (%) =
VAR CurrentSprint = 'Running Total'[Sprintnumber]
VAR CurrentCompany = 'Running Total'[CompanyID]
VAR CurrentTeam = 'Running Total'[TeamID]
VAR FilteredTable =
    FILTER(
        'Running Total',
        'Running Total'[TeamID] = CurrentTeam &&
        'Running Total'[Sprintnumber] = CurrentSprint
       

    )
RETURN
    DIVIDE(
        [Rolling Total (€)],
        CALCULATE(
            SUM([Rolling Total (€)]),
            FilteredTable
        )
    )  


I am a beginner in the Power BI area and would be grateful for any help!
Need Help

2 Replies

    • Alina12's avatar
      Alina12
      Helper I
      2 years ago

      Unfortunately, this did not work because I do not have any CompanyNames in the JiraExport_Sprints_SharepointFolder table. To explain: The stories are not assigned to any company and should always be distributed to all companies via the percentage share (Rolling Total (%)).

       

      Anyway, the Youtube video helped me a lot and at least the grand total column now works with this measure:


      FINAL_DeliveredValue = IF(HASONEVALUE(Companies[CompanyName]),'KeyMeasures - DeliveredValue'[2Delivered Value],SUMX(Values(Companies[CompanyName]),'KeyMeasures - DeliveredValue'[2Delivered Value]))

       

      However, only the matrix table with the grand total column works (at least somewhat). As soon as I want to display the row grand total or create another visual (e.g. a simple table with companies and delivered value), it doesn't work again. What am I doing wrong? Can I optimize the measure 2Delivered Value or now also the FINAL_DeliveredValue so that I can use it without any problems and don't have to create a new measure for each visual?