Forum Discussion

Uzi2019's avatar
Uzi2019
Icon for Community Champion rankCommunity Champion
5 years ago
Solved

Incorrect grand Total

Hi Experts,
 
I have a measure below but when I put it on Matrix against Name and Category so It gives me incorrect grand total. Row wise showing correct value.
 
Measure=
SUMX(
SUMMARIZE('Table A',
'Table A'[Category]
),
IF([Measure 1] <> BLANK(),[Measure 1],
IF( MAX('Table B'[Name]) IN {"A","A1","A2"},
[Cost Measure],
[Cost Measure 2] * [Actual Measure]
)
)
)
 
I tried many blogs and solutions with HASONEVALUE and SUMX but no use with my scenario.
 
Can anyone please help to get the correct total in matirx visual?
 
Need Help
  • Tahreem24's avatar
    Tahreem24
    5 years ago

    Hi Uzi2019 ,
    Try to replace MAX with SELECTEDVALUE so that It will behave according to  your slicer value.

    Measure=
    SUMX(
    SUMMARIZE('Table A',
    'Table A'[Category]
    ),
    IF([Measure 1] <> BLANK(),[Measure 1],
    IF( SELECTEDVALUE('Table B'[Name]) IN {"A","A1","A2"},
    [Cost Measure],
    [Cost Measure 2] * [Actual Measure]
    )
    )
    )

     

8 Replies

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    5 years ago

    Hi Uzi2019 ,
    Try to replace MAX with SELECTEDVALUE so that It will behave according to  your slicer value.

    Measure=
    SUMX(
    SUMMARIZE('Table A',
    'Table A'[Category]
    ),
    IF([Measure 1] <> BLANK(),[Measure 1],
    IF( SELECTEDVALUE('Table B'[Name]) IN {"A","A1","A2"},
    [Cost Measure],
    [Cost Measure 2] * [Actual Measure]
    )
    )
    )

     

    • Uzi2019's avatar
      Uzi2019
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Awesome!! Thanks a lot Tahreem24  for your help. After replacing Max with SelectedValue I am getting Total  very close to Actual sum with very litte discrepency. But it was helpful.

       

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Uzi2019 , Try a measure like ( Small changes)

     

    Measure=
SUMX(
	SUMMARIZE('Table A',
	'Table A'[Category],"_1", IF([Measure 1] <> BLANK(),[Measure 1],
						IF( MAX('Table B'[Name]) IN {"A","A1","A2"},
						[Cost Measure],
						[Cost Measure 2] * [Actual Measure]
						)
						))
, [_1])
    • Uzi2019's avatar
      Uzi2019
      Icon for Community Champion rankCommunity Champion
      5 years ago

      AlB ,

      yeah name is from table B & sorry I can not shared pbix as it is revenue related data.

       

      amitchandak ,

      Thanks for your reply. I tried your measure but still getting same result.( i.e incorrect grand total)
      Can you please give some other solution.?

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Uzi2019 

    From what table is Name? TableB? Can you share the pbix, or a pbix with mock data that reproduces the problem?

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    5 years ago
    I have a slicer for Name which list of values. When I select All values from slicer so only Else part (Cost Measure 2 * Actual Measure) part gets executed.

    But I want to show sum of all names means Cost Measure and plus Cost Measure 2 * Actual Measure.
     
    Please help me out as I have a report delivery in next one Hour.