Save Measure-values in Table when removing Attribute-Columns

[Anonymous]

Hello,

I want to make a tabke only containing the first 4 key-columns and the last "Relative Frequency". I want to keep the same Relative Frequency percentages even when I remove all the attributes.

The only way I've figured out to do this is to make the table I want and then save it to Excel and then open the Excel-sheet in PowerBI and remove the attributes.

If anyone have any suggestions to do it all in PowerBI I'd be very interested in that! 🙂

This is the table I have:

And this is what I want (with the same Relative Frequency as the image above). This is made by "Export data":

I hope you can help!

Thank you 🙂

[Fowmy]

@Anonymous 

You can use SELECTCOLUMNS to create a new table keep only the required columns as follows:

New Table = 

SELECTCOLUMNS (
    Table1,
    "Combined Key", Table1[Combined Key],
    "Key1", Table1[Combined Key1],
    "Key2", Table1[Combined Key2],
    "Key3", Table1[Combined Key3],
    "Freq %", Table1[Frequency %]
)

[Anonymous]

Thank you for response. The Relative Frequency is a Calculated Measure and I've been having problems making that a column. Sorry I forgot to mention that in the above description.
And if I use the Calculated Measure "Relative Frequency" in the DAX-formula to create the table it gives incorrect results.

[Fowmy]

@Anonymous 

Not, clear enough, please share a sample PBIX file and the expected results. By the way I you need to calculate the frequency while create a the new table then try the following:

New Table = 
ADDCOLUMNS (
SELECTCOLUMNS (
    Table1,
    "Combined Key", Table1[Combined Key],
    "Key1", Table1[Combined Key1],
    "Key2", Table1[Combined Key2],
    "Key3", Table1[Combined Key3]
),
"Freq %", CALCULATE ( enter your freq formula here )
)

[Anonymous]

Hi Fowmy,

Thank you for your response!

I have selected a small part of the dataset to create the dummydata.

Please see the pbix-file below:

https://easyupload.io/ndyltg

I hope you can help!

Thank you once again 🙂

[Anonymous · Accepted solution]

Hi @Anonymous ,

You couldn't use the same logic of your measure to create a column. Here I update your measure and find which cause your issue.

Relative Frequency =
VAR _PART1 =
    DISTINCTCOUNT ( 'dummydata-whatIhave'[MATNR] )
VAR _PART2 =
    CALCULATE (
        DISTINCTCOUNT ( 'dummydata-whatIhave'[MATNR] ),
        ALLEXCEPT ( 'dummydata-whatIhave', 'dummydata-whatIhave'[key-combined] )
    )
VAR _RATE =
    DIVIDE ( _PART1, _PART2 )
RETURN
    _RATE

Your measure is created by Part1 and Part2, Part1 will be caculated by filter of columns in Matrix. Part1 will casue incorrect result in calculated column. You need to add the filter in your new code.

You can try this code to achieve your goal.

Table = 
VAR _AddRate =
    ADDCOLUMNS (
        'dummydata-whatIhave',
        "Rate",
            VAR _PART1 =
                CALCULATE (
                    DISTINCTCOUNT ( 'dummydata-whatIhave'[MATNR] ),
                    ALLSELECTED ( 'dummydata-whatIhave'[MATNR] )
                )
            VAR _PART2 =
                CALCULATE (
                    DISTINCTCOUNT ( 'dummydata-whatIhave'[MATNR] ),
                    ALLEXCEPT ( 'dummydata-whatIhave', 'dummydata-whatIhave'[key-combined] )
                )
            VAR _RATE =
                DIVIDE ( _PART1, _PART2 )
            RETURN
                _RATE
    )
VAR _SUMMARIZE =
    SUMMARIZE (
        _AddRate,
        'dummydata-whatIhave'[key-combined],
        'dummydata-whatIhave'[key1],
        'dummydata-whatIhave'[key2],
        'dummydata-whatIhave'[key3],
        [Rate]
    )
RETURN
    _SUMMARIZE

Result is as below.

Best Regards,
Rico Zhou

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