How to show Group Total with Lowest detailed data in report

[ojhassonu]

Dear Folks,

I have a requirement where I have to show group total along with the detailed data.

Source tables:

DATA TABLE:

MAPPING TABLE:

REQUIRED OUTPUT:

I tried measure below measure but couldn't worked.

total pillar wgt =

IF ( ISBLANK ( SUM (DATA[WEIGHT]) ), BLANK (),

CALCULATE ( SUM (DATA[WEIGHT]), REMOVEFILTERS (DATA[Metric]) ) )

Power BI file with sample data

https://drive.google.com/file/d/1xXuMayLHiNu4IlDhyhR4yWiLkfFrbQ-h/view?usp=drive_link

please advise to achive the Total Weight for Pillar and show it as required output format.

Regards,

Sonu

[ojhassonu]

Thanks for reply.

Please find below my actual requirement "Matrix format" screenshot. As in matrix, I'd like to show pillar weight next to Pillar column, can't add matrix. However tried with column but it doesn't work. I used the DAX and its showing same value for all.

pillarwtcol=

IF(NOT(ISBLANK(SELECTEDVALUE(MAPPING[PILLAR]))),

SUMX(FILTER(ALL(MAPPING), MAPPING[PILLAR] = SELECTEDVALUE(MAPPING[PILLAR])), SUMX(SUMMARIZE(FILTER(ALL(DATA), DATA[METRIC] = MAPPING[METRIC] ), DATA[METRIC], "Avg", AVERAGE(DATA[WEIGHT])), MAX([Avg]

))),

SUMX(SUMMARIZE(MAPPING, MAPPING[PILLAR]), AVERAGE(DATA[WEIGHT])))

and 

Pillar WeightCol =

SUMX(

    FILTER(ALL(MAPPING),

    MAPPING[PILLAR] = SELECTEDVALUE(MAPPING[PILLAR])

    ),

    SUMX(

        SUMMARIZE(

            FILTER(

            ALL(F_Data_Tabular),

            F_Data_Tabular[METRIC] = MAPPING[METRIC] ),

             F_Data_Tabular[METRIC],

            "Avg",

            AVERAGE(F_Data_Tabular[WEIGHT])

            ),

            MAX([Avg]

            )

            )

        )

please advise.

[TobyNye]

Hi, I downloaded your sample pbix and built this measure which seems to work as you described:

The reason for the average in the SUMMARIZE function is because there were multiple rows per metric in your data table and your desired output had the average value, you can change that to whichever aggregation suits your needs if that's not what you want. The measure is effectively removing the row level filter context, putting it back on for the pillar, then going into the DATA table where the metric id's match and summing the sum of the average weight from the data table. Raw measure code:

Pillar Weight =

SUMX(FILTER(ALL(MAPPING), MAPPING[PILLAR] = SELECTEDVALUE(MAPPING[PILLAR])), SUMX(SUMMARIZE(FILTER(ALL(DATA), DATA[METRIC] = MAPPING[METRIC] ), DATA[METRIC], "Avg", AVERAGE(DATA[WEIGHT])), MAX([Avg]

)))

Hope that helps, let me know if you have any issues or further questions.

[ojhassonu]

Great Thanks for the reply. This dax is almost working, however 

1. This is not showing Grand Total of pillar weight.

2. This DAX is not working in Matrix view, could you help me same as for calculated column. That would show grand total as well

Best Regards,

[TobyNye]

Could you show me the matrix or share a file with it where it's not working? I believe the issue will be related to exactly which fields from which table you are wanting to have within it. I imagine the measure will just need a few tweaks to fit your exact use case. The reason it returns blank on total is because it's looking for SELECTEDVALUES of pillar and metric in order to get the filter context we want at a row level. You could remedy this by adding an IF statement to the beginning such that your measure would look like:

Pillar Weight =

IF(NOT(ISBLANK(SELECTEDVALUE(MAPPING[PILLAR]))),

SUMX(FILTER(ALL(MAPPING), MAPPING[PILLAR] = SELECTEDVALUE(MAPPING[PILLAR])), SUMX(SUMMARIZE(FILTER(ALL(DATA), DATA[METRIC] = MAPPING[METRIC] ), DATA[METRIC], "Avg", AVERAGE(DATA[WEIGHT])), MAX([Avg]

))),

SUMX(SUMMARIZE(MAPPING, MAPPING[PILLAR]), AVERAGE(DATA[WEIGHT])))

If that doesn't work, let me know and I'll recreate the .pbix I had and give it another look.

[ojhassonu]

Thanks for reply.

Please find below my actual requirement "Matrix format" screenshot. As in matrix, I'd like to show pillar weight next to Pillar column, can't add matrix. However tried with column but it doesn't work. I used the DAX and its showing same value for all.

pillarwtcol=

IF(NOT(ISBLANK(SELECTEDVALUE(MAPPING[PILLAR]))),

SUMX(FILTER(ALL(MAPPING), MAPPING[PILLAR] = SELECTEDVALUE(MAPPING[PILLAR])), SUMX(SUMMARIZE(FILTER(ALL(DATA), DATA[METRIC] = MAPPING[METRIC] ), DATA[METRIC], "Avg", AVERAGE(DATA[WEIGHT])), MAX([Avg]

))),

SUMX(SUMMARIZE(MAPPING, MAPPING[PILLAR]), AVERAGE(DATA[WEIGHT])))

and 

Pillar WeightCol =

SUMX(

    FILTER(ALL(MAPPING),

    MAPPING[PILLAR] = SELECTEDVALUE(MAPPING[PILLAR])

    ),

    SUMX(

        SUMMARIZE(

            FILTER(

            ALL(F_Data_Tabular),

            F_Data_Tabular[METRIC] = MAPPING[METRIC] ),

             F_Data_Tabular[METRIC],

            "Avg",

            AVERAGE(F_Data_Tabular[WEIGHT])

            ),

            MAX([Avg]

            )

            )

        )

please advise.