Add custom field to selectedvalue column in matrix

[Analitika]

I have matrix where are column from table1[types], which contains 3 types 1. fuel 2.diesel 3. Adblue, then i link this table to table2 which has two columns, 1. sum1 2. sum2

i want to show in matrix 4 type Costs and get value from column sum2

Types  Sum

fuel sum(table2[sum1])

diesel sum(table2[sum1])

Adblue   sum(table2[sum1])

Costs   sum(table2[sum2])  ' <--- Custom field

How to do that?

[Anonymous]

Hi @Analitika,

You can create a parameter table with all category and custom category types and use this to replace raw fields.
Then you need to write a measure formula with switch function and selectedvalue to check current row content and return corresponding expression results.

Write Conditional Statement Using SWITCH in DAX and Power BI 

Regards,

Xiaoxin Sheng

[Analitika]

@Anonymous 

No i cant, as category type then not included in main table and not showing in result matrix

[Anonymous]

HI @Analitika,

How about manually create them? You can use datatable function to define them if they not too many records.
BTW, these categories not required to store in the main table, they are interacting with DAX functions and not need to link to raw table.
Regards,
Xiaoxin Sheng

[Analitika]

@Anonymous  you can try to help me create 1 million rows

[Anonymous]

HI @Analitika,

Why do you mean to create 1 million rows? Can you please share some dummy data with your raw table structures?

How to Get Your Question Answered Quickly 
BTW, I think you only need the raw table fields and append the extra field. then you can use it as axis, and write a measure formula to replace the result of the new add field type to summary column 2.

New Table =
UNION ( VALUES ( Table1[Type] ), ROW ( "Type", "Costs" ) )

Measure =
VAR currType =
    SELECTEDVALUE ( 'New Table'[Type] )
RETURN
    IF (
        currType = "Costs",
        CALCULATE (
            SUM ( Table2[Sum2] ),
            FILTER ( ALLSELECTED ( Table1 ), [Type] = currType )
        ),
        CALCULATE (
            SUM ( Table2[Sum1] ),
            FILTER ( ALLSELECTED ( Table1 ), [Type] = currType )
        )
    )

Regards,

Xiaoxin Sheng

[Analitika]

@Anonymous 

But  currType never be equal to "Costs", as 'New Table' does not have "Costs" type, and always will work else statment in your exapmle. "Costs" field exists only in Table1[Type]

https://filebin.net/fxc8y76kim6yisxk

[Anonymous]

HI @Analitika,

If you want to show the expand fields that not exist in raw table, you need to use unconnected table fields as category or they will be filtered by power bi before you do custom with them.

Sample formula:

Measure = 
VAR suma =
    SUM ( Table1[sum] )
VAR savi =
    SUMX (
        'Types(Unconnected)',
        CALCULATE (
            CALCULATE (
                CALCULATE ( SUM ( Table1[sum] ), VALUES ( Table1[Route_ID] ) )
                    + MAX ( Table1[Fuel Consumped] ),
                Table1[type code] <> 2
            )
                + SUM ( Table1[Fuel filled] ),
            FILTER ( Table1, [type code] IN VALUES ( 'Types(Unconnected)'[id] ) ),
            VALUES ( 'Date'[sort_month] )
        )
    )
VAR bp = suma - savi
VAR marza =
    DIVIDE ( bp, suma )
RETURN
    IF (
        ISINSCOPE ( Types2[Name] ),
        SWITCH (
            SELECTEDVALUE ( Types2[Name] ),
            "Sales", IF ( ISINSCOPE ( 'Types(Unconnected)'[Name] ), BLANK (), suma ),
            "Costs",
                IF (
                    SELECTEDVALUE ( 'Types(Unconnected)'[Name] )
                        IN { "Fuel, consumed", "Ad Blue, consumed" },
                    SWITCH (
                        SELECTEDVALUE ( 'Types(Unconnected)'[Name] ),
                        "Fuel, consumed", IF ( MAX ( 'Date'[Date] ) IN VALUES ( Table1[Date] ), -1 ),
                        "Ad Blue, consumed", IF ( MAX ( 'Date'[Date] ) IN VALUES ( Table1[Date] ), -2 ),
                        BLANK ()
                    ),
                    savi
                ),
            "Profit", IF ( ISINSCOPE ( 'Types(Unconnected)'[Name] ), BLANK (), bp )
        )
    )

Notice:

1. Since relationship has been breaks, it mean you need to manually apply filter on row table fields based on current fields values.

2. -1,-2 are placeholders of added field values, I already add if statement and conditions to remove not match fields display on the visual.

3. I modify the calculate formula but 'savi' part seems not calculate correctly, you can try to fix them. (logic: calculate without raw types table and use 'in' operator to filter calculate result with new table field values)
Regards,

Xiaoxin Sheng

[Analitika]

@Anonymous 

3. I modify the calculate formula but 'savi' part seems not calculate correctly, you can try to fix them. (logic: calculate without raw types table and use 'in' operator to filter calculate result with new table field values)

that because i ask for help here, in that way it totaly wrong counting on totals and not include placeholders values, also in example only 2 Types(Unconnected), but imagine what i have them over 100

[Anonymous]

Hi @Analitika,

>>that because i ask for help here, in that way it totaly wrong counting on totals and not include placeholders values,

They may related to multiple aggregates on the calculation, take a look at the following blog if helps.

Measure Totals, The Final Word 

>> also in example only 2 Types(Unconnected), but imagine what i have them over 100

Ok, I think my formulas may not suitable to handle this scenario.

How about direct append some template blank row to the raw table with corresponding type codes? After these steps, these types exist in your table and you can simply use DAX formulas to replace display contents.

Regards,

Xiaoxin Sheng