In matrix visual, calculate total gross value of level 1 and display it in level 2

[jay_patel]

I have a matrix visual IN POWER BI (REFER BELOW IMAGE), in which we are displaying TSO and ABM wise sales data ,

TSO has level 1 and ABM has level 2 .

Gross value for level 1 comes from below dax: IF(MAX(POsition[LevelNo])=1,CALCULATE(IFERROR(SUM(Online_view_TerritoryAnalysis_Incentive[SalesAmt])/[value of man power],0)))

Now, for level 2 I want to display summation of all values of level 1 . (Kindly ignore current displayed gross value at level 2 )

So , values at level 2 would be 816166.51+816166.51+816166.51+816166.51+816166.51+816166.51 = 4,896,999.06

@amitchandak 

[Anonymous · Accepted solution]

Hi @jay_patel ,

1. Create a MEASURE to find the sum of sales with a level of 1.

Measure =
CALCULATE(IFERROR(SUM('Table'[SalesAmt])/MAX('Table'[value of man power]),0),FILTER(ALL('Table'),'Table'[LevelNo] = 1)))

2. Create a MEASURE to get the sum of level 2.

Measure 2 =
SUMX(FILTER(ALL('Table'),'Table'[LevelNo] = 1),'Table'[Measure])

3. Create the MEASURE to display different data according to different levels.

Measure 3 =
IF(MAX('Table'[LevelNo]) = 1,
'Table'[Measure],
IF(MAX('Table'[LevelNo]) = 2, 'Table'[Measure 2]))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jay_patel]

Hi @Anonymous,

 LevelNo comes from Position table and SalesAmt comes from Sales table and both the tables are related on the basis of vHQCode . I am not sure if the dax is filtering out Level wise values .

positionsales

output

[Anonymous]

Hi @jay_patel ,

Is the result shown in measure3 not what you expected? If not, please take a screenshot or something to show what you expect. Also, are the two tables joined via vHQCode many-to-many?

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.