How to add an extra totals column to Matrix to show only the average

[DeBIe]

Hey all,

I have a basic matrix which is showing the total documents per month for a whole year. My customer has a new requirement. She would like to see an extra column at the end with the average. In addition to that she would like to to display the value of the current month in red IF it is below the total average value. This is what I have now:

January-23	February-23	March-23	Total
20	20	10	50
10	10	50	70

This is what the end result should look like

January-23	February-23	March-23	Total	TotalAvg
20	20	10	50	16,6
10	10	50	70	23,3

Thank you for your time!

[DeBIe]

@v-jianboli-msft 

Theres one more question I would like to ask. I forgot to mention that I would like to display the Name of the company instead of the fk_company_id. The name of the company is in the related lookup table Company_Lookup.

Relation: DocumentFinal[fk_company_id] * -> 1 Company_Lookup[id]

I have modified the measure but for some reason the Total is not showing:

_Measure = 
    var _a = CALCULATE(COUNT(DocumentFinal[InvoiceID-DocType]),ALLEXCEPT(Company_Lookup,Company_Lookup[Name]))
    var _b = CALCULATETABLE(SUMMARIZE(DocumentFinal,Calendar_YearMonthName[YearMonthNumber],"Value", [Total Invoices Uploaded]),USERELATIONSHIP('Calendar'[Dates],DocumentFinal[CreateDate]))
    var _c = COUNTROWS(_b)
    return
    SWITCH(TRUE(),
    ISINSCOPE(Calendar_YearMonthName[YearMonthNumber])&&MAX(Calendar_YearMonthName[YearMonthNumber])<>"Total", [Total Invoices Uploaded],
    ISINSCOPE(Calendar_YearMonthName[YearMonthNumber])&&MAX(Calendar_YearMonthName[YearMonthNumber])="Total",_a,_a/_c)

The color is also not showing correct results when I modified the measure for color formatting:

_TestColor = 
VAR _a =
    CALCULATE (
        COUNT ( DocumentFinal[InvoiceID-DocType] ),
        ALLEXCEPT ( Company_Lookup,Company_Lookup[Name] )
    )
VAR _b =
    CALCULATETABLE (
        SUMMARIZE (
            DocumentFinal,
            Calendar_YearMonthName[YearMonthNumber],
            "Value", [Total Invoices Uploaded]
        ),
        USERELATIONSHIP ( 'Calendar'[Dates], DocumentFinal[CreateDate] )
    )
VAR _c =
    COUNTROWS ( _b )
RETURN
    IF (
        SELECTEDVALUE ( Calendar_YearMonthName[YearMonthNumber] ) = FORMAT ( TODAY (), "YYYYMM" )
            && [_MeasureBookbot] < _a / _c,
        "Red",
        "Black"
    )

[DeBIe]

@v-jianboli-msft 

If you have time, can you please help me with finishing this requirement?

Thanks a lot.

[v-jianboli-msft · Accepted solution]

Hi @DeBIe ,

Please try:

First create a new table and create relationship between the tables:

Then apply these measure as I mentioned before:

Measure = 
var _a = CALCULATE(COUNT(DocumentFinal[InvoiceID-DocType]),ALLEXCEPT(DocumentFinal,DocumentFinal[fk_company_id]))
var _b = CALCULATETABLE(SUMMARIZE('DocumentFinal','Table'[Month Name],"Value",[Total Documents Uploaded]),USERELATIONSHIP('Calendar'[Dates],DocumentFinal[CreateDate]))
var _c = COUNTROWS(_b)
return 
SWITCH(TRUE(),
ISINSCOPE('Table'[Month Name])&&MAX('Table'[Month Name])<>"Total",[Total Documents Uploaded],
ISINSCOPE('Table'[Month Name])&&MAX('Table'[Month Name])="Total",_a,
_a/_c)

Format Color =
VAR _a =
    CALCULATE (
        COUNT ( DocumentFinal[InvoiceID-DocType] ),
        ALLEXCEPT ( DocumentFinal, DocumentFinal[fk_company_id] )
    )
VAR _b =
    CALCULATETABLE (
        SUMMARIZE (
            'DocumentFinal',
            'Table'[Month Name],
            "Value", [Total Documents Uploaded]
        ),
        USERELATIONSHIP ( 'Calendar'[Dates], DocumentFinal[CreateDate] )
    )
VAR _c =
    COUNTROWS ( _b )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Month Name] ) = FORMAT ( TODAY (), "MMMM" )
            && [Measure] < _a / _c,
        "Red",
        "Black"
    )

Output:

PS: The result is calculated with the current date being March, if it is April, the result will be different.

Best Regards,

Jianbo Li

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

[DeBIe]

Thank you very much @v-jianboli-msft, this works!

[v-jianboli-msft]

Hi @DeBIe ,

Could you tell me what your 'Calendar' [Dates] looks like? From your description, it seems to have an inactive relationship with DocumentFinal[CreateDate]. If this is the case, the columns of your matrix visual will be date by date and average will be calculated based on dates, not months.

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[DeBIe]

Hi @v-jianboli-msft ,

Sorry for my late response. I did not see that you had send a reply :(. 

I've set up a pbix.file. Can you have a look? I did not find the option to upload the pbix. file here, so I uploaded it to wetransfer.
https://we.tl/t-BeK9C8z4q2 

In most of my report I am using the active relationships between [DocumentFinal](Bookdate) -> [Calendar](Dates). However, sometimes I need to use the inactive relationship [DocumentFinal](CreateDate) -> [Calendar](Dates).

Thank you very much for your time again.

[v-jianboli-msft]

Hi @DeBIe ,

Baseed on your description, I have created a simple sample:

Pleass try:

First create a new table and create relationship between the tables:

Table 2 = UNION(SUMMARIZE('Table','Table'[YearMonth]),{"Total"})

Then apply the measure to the matrix visual:

Measure = SWITCH(TRUE(),
ISINSCOPE('Table 2'[YearMonth])&&MAX('Table 2'[YearMonth])<>"Total",SUM('Table'[Value]),
ISINSCOPE('Table 2'[YearMonth])&&MAX('Table 2'[YearMonth])="Total",CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[ID])),
AVERAGEX(SUMMARIZE('Table','Table'[YearMonth],"Value",SUM('Table'[Value])),[Value])
)

Rename column subtotals:

Output:

Then use this measrue to conditional formatting the value:

Format Color =
IF (
    MAX ( 'Table'[YearMonth] ) = FORMAT ( TODAY (), "MMM-YY" )
        && [Measure]
            < AVERAGEX (
                SUMMARIZE (
                    ALLEXCEPT ( 'Table', 'Table'[ID] ),
                    'Table'[YearMonth],
                    "Value", SUM ( 'Table'[Value] )
                ),
                [Value]
            ),
    "Red",
    "Black"
)

Final output:

Best Regards,

Jianbo Li

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

[DeBIe]

Hi @v-jianboli-msft ,

Thank you so much for the suggested solution. From your example I can see that it is exactly what I need. I was hoping to achieve it in my model but my model is set up a bit different than I presented (sorry for that). However I don't think it changes a lot in the formula, but I don't know how to get the last part of the formula correct for the Averagex function. 

This is how my data really looks like:

DocumentID	CompanyID	CreateDate
1	991	1-1-2023
2	652	2-1-2023
3	378	3-1-2023
4	899	4-1-2023
5	951	5-1-2023
6	951	1-2-2023
7	650	2-2-2023
8	331	3-2-2023
9	588	4-2-2023
10	417	5-2-2023
11	461	6-2-2023
12	421	1-3-2023
13	381	2-3-2023
14	951	3-3-2023
15	378	4-3-2023
16	261	5-3-2023
17	221	6-3-2023
18	378	7-3-2023
19	141	8-3-2023
20	951	9-3-2023

Good to know is that the CreateDate is a inactive relationship in my model. To calculate the Total documents per month I am using this formula:

Total Documents Uploaded = CALCULATE(COUNT(DocumentFinal[DocumentID]),USERELATIONSHIP(DocumentFinal[CreateDate],'Calendar'[Dates]))

This is how far I've got based on your suggested measure:

DocumentsUploadedSolution = SWITCH(TRUE(),
    ISINSCOPE('Calendar'[Dates])&&MAX('Calendar'[Dates])<>"Total", CALCULATE(COUNT(DocumentFinal[DocumentID]),USERELATIONSHIP(DocumentFinal[CreateDate],'Calendar'[Dates]),
    ISINSCOPE('Calendar'[Dates])&&MAX('Calendar'[Dates])="Total",CALCULATE(COUNT(DocumentFinal[DocumentID]),USERELATIONSHIP(DocumentFinal[CreateDate],'Calendar'[Dates]),ALLEXCEPT(DocumentFinal,DocumentFinal[fk_company_id]))),
    AVERAGEX(SUMMARIZE(DocumentFinal,DocumentFinal[CreateDate],"Value",CALCULATE(COUNT(DocumentFinal[DocumentID]),USERELATIONSHIP(DocumentFinal[CreateDate],'Calendar'[Dates])))))

I think we're almost there. Thank you so much for your time. Greatly appreciated.