Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
5 years ago

Add custom field to selectedvalue column in matrix

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 mat...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Analitika
Post Prodigy
5 years ago

Anonymous  you can try to help me create 1 million rows

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Analitika
      Post Prodigy
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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