How To Add DAX to Matrix Subtotal for each Colummn

[Shoukry]

Hi

I 'm using Matrix Control and I have Group By Country and I need to add different calculation or formula in the Subtotal section for each column

for example:

I need to calculate the Average of sales Percentage in the first column, and

I need to add another calculation in Subtotal of the second Column to Divide the Total Revenue on a value coming from another column

so How can I do that for Subtotal & Grand Total for Each Column?

and If I add it as a DAX in Measure how can I tell the Matrix Control to view this Mesure in the subtotal section under each column?

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[Shoukry]

Hi Greg

I think this 's not actually what I'm Asking about because I don't have a problem with a Mesure or DAX syntax, my problem is how can I manage the Subtotal in the Group Section for Example on a report writing Tools such as Crystal Reports or SSRS you will find the page in design mode divided to Header, Details, and Footer and if you would like to add a Group Section you will be able to use  a Formula in the Group Section Also

in the Olab DB, you can Add a Cube Rule to Manage Whatever you need,
but in Power BI DeskTop you just have a Flat Page not divided to anything and when you need to use a table with Group By Section you have to use Matrix Control and when you try to add Subtotal in the Matrix Control you can Just Show Or Hide this section , so what If I need to Add a calculation in the Sub or Grand Total such as the Below Image, what can I do ?

[Ashish_Mathur]

Hi,

The measure for occupancy should look something like this

=IF(HASONEVALUE(Data[Hotels]),[your measure for calculating occuancy],AVERAGEX(VALUES(Data[Hotels]),[your measure for calculating occuancy]))

Hope this helps.

[Shoukry]

Dears

Does anybody solution an Idea for my problem?

such as, should I build my report by specific way?

or, should I replace the Matrix control with another control has the smart features than the Matrix?

Please Advice ...

[Greg_Deckler · Accepted solution]

I just posted this general pattern for this here as the Matrix Measure Total Triple Threat Rock & Roll Quick Measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[Greg_Deckler]

Not to be that guy but with all due respect, I think your problem is what I indicated initially. If "Occupancy %" is just a column, then you could use the default "average" aggregation and all would be well. But, if as I suspect, "Occupancy %" is a measure then you are running into the issue that I specified:

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

But, just to demonstrate specifically what I am talking about now that you have provided some sample data, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

You could create this measure:

Occupancy Measure = 
VAR myCountry = MAX([Country])
VAR myTotal =  
    AVERAGEX(
        FILTER(
            SUMMARIZE(
                ALLSELECTED(Hotels),
                Hotels[Country],
                Hotels[Hotel],
                "Occupancy%",[Normal Occupancy %]            ),
            Hotels[Country]=myCountry
        ),
        [Occupancy%]
    )
RETURN IF(
            HASONEVALUE(Hotels[Hotel]),
            [Normal Occupancy %],
            myTotal
        )

The part in red is your current Occupancy measure. You will get something like this: