topic DAX sum and max based on the hierarchy in matrix visual in Power BI in DAX Commands and Tips

DAX sum and max based on the hierarchy in matrix visual in Power BI

SriBhaskar — Fri, 08 Sep 2023 19:36:28 GMT

Below is my data set 'SOWSMTData' that I am imported in power bi.

SOW Name	SMTName	TotalSOWValue	JanActual	FebActual
XXX	Vijay	50	10	8
XXX	Vijay	50	11	9
YYY	Vijay	70	0	9
YYY	Vijay	70	8	7
YYY	Vijay	70	10	15
ZZZ	Siva	30	5	4
ZZZ	Siva	30	6	4
BBB	Siva	40	10	5
AAA	Sai	80	10	4
CCC	Sai	90	7	6
CCC	Sai	90	8	10
XXX	Sai	50	3	0
YYY	Mohan	70	2	5
YYY	Mohan	70	5	8

In the above I have SOWName, SMTName and SOWValue. One SOW mapped to different SMTNames. When I take the SOWName I need to get the same value in the matrix. I need the below output in Matrix Visual in Power BI. Under SMTName the SOW is drilldown like below.

SMTName	MTotalSOWValue	JanActual	FebActual
Vijay	120	39	48
XXX	50	21	17
YYY	70	18	31
Siva	70	21	13
ZZZ	30	11	8
BBB	40	10	5
Sai	220	28	20
AAA	80	10	4
CCC	90	15	16
XXX	50	3	0
Mohan	70	7	13
YYY	70	7	13

I am facing issue while calculating the 'TotalSOWValue' measure in the above matrix.

I tried the below two measures

MTotalSOWValue = CALCULATE(MAX(SOWSMTData[TotalSOWValue]),ALL(SOWSMTData[SOWName))

MTotalSOWValue =

SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            SOWSMTData,
            SOWSMTData[SMTName],

            SOWSMTData[SOWName],
        ),
        "MaxSOWValue",
            CALCULATE(
                MAX( SOWSMTData[SOWName] )
            )
    ),
    [MaxSOWValue]
)

I am not getting the subtotals correct by using above two measures. Please guide me if there is anything I missed.

I think becuase the same SOWName relates to different SMTName. Please suggest.

Re: DAX sum and max based on the hierarchy in matrix visual in Power BI

nirali_arora — Mon, 11 Sep 2023 07:09:47 GMT

It seems like you require the following formula :

Maxx(Values(Table[SOW]), calculate(Sum(SOWSMTData[TotalSOWValue])))

The similar concept is explained in this video : https://youtu.be/cN8AO3_vmlY?t=22980

Re: DAX sum and max based on the hierarchy in matrix visual in Power BI

Anonymous — Mon, 11 Sep 2023 08:43:12 GMT

Hi SriBhaskar ,

Here are the steps you can follow：

1. Create measure.

Measure = MAXX( FILTER(ALL('Table'), 'Table'[SOW Name]=MAX('Table'[SOW Name])&&'Table'[SMTName]=MAX('Table'[SMTName])),[TotalSOWValue])

True = var _table1= SUMMARIZE('Table','Table'[SMTName],'Table'[SOW Name],"Value1",[Measure]) return IF( HASONEVALUE('Table'[SOW Name]),[Measure], SUMX(_table1,[Value1]))

2. Result:

Best Regards,

Liu Yang

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