Unable to create a summarised column to place in Legend for Map

[Anonymous]

HI,

I am unable to create a calculated field for a legend for a map. I have a raw data set which has many lines per postcode, which I need to group by. And in that table are two variables I need to divide to get an average for the whole postcode. And that is what I need to chart. 

Example data:

Postcode	Value_A	Value_B
4500	10000	20000
4500	58	100
2300	20000	30000
2300	60	90

I want to chart divide(sum(Value_A),sum(Value_B)). When I place these values in a table to see what the values look like. Doing the below as a simple measure is fine. However - measures can't be added as a Legend it seems.

LVR_MAP = SUMX(SUMMARIZE(table2,Table2[Postcode],"DIVIDE", DIVIDE(sum(SummaryTable[Value_A]),SUM(SummaryTable[Value_B]))),[DIVIDE])

Putting the above as a calculated column gives a different result, which is not right.

How do I do this in a column where the grouping is done correctly by postcode before the calculation is done?

Thanks,

Arch

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think there are multiple ways to achieve this requirement. Here I provide a method to create a calculation table.

NewTable = 
SUMMARIZE(
    'Table1',
    'Table1'[Postcode],
    "Value_A", SUM('Table1'[Value_A]),
    "Value_B", SUM('Table1'[Value_B]),
    "Legend", DIVIDE(SUM('Table1'[Value_A]), SUM('Table1'[Value_B]), 0)
)

Best Regards,

Wearsky

[Anonymous]

Actually - *sigh* your solution works Ok. I had to make some modifications - but it has put me on the right track. Thank you very much!! Happy days 🙂 🙂

[Anonymous]

Thanks Wearsky,

Unfortunately I do not think it reads Value_A in this expression. It tries to find Value_A in Table1, but I get an error stating Value_A in table 'Table1' cannot be found... 

Thanks for your attempt!

Regards - Arch

[Anonymous]

Hi @Anonymous ,

Could you share the whole sample data and expected outputs?
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Best Regards,

Wearsky

[Anonymous]

Thank you. This is the source data below:

Postcode	Value_A	Value_B
4500	10000	20000
4500	58	100
2300	20000	30000
2300	60	90

Through using DAX, I want to create a summary table. That table should look like below:

Postcode	Value_A	Value_B	Legend
4500	10058	20000	0.5029
2300	20060	30090	0.666667

Where Legend = sum(Value_A)/sum(Value_B) grouped by Postcode. The Legend column must form part of the Legend field for the Map chart. I cannot get DAX to summarse Legend correctly and it be a column ready to chart...

Thanks a lot!

Regards - Arch

[Ashish_Mathur]

Hi,

Not sure of what you want.  In the Query Editor, right click on the first column and select "Unpivot Other Columns".  Rename Attribute to Value type.  You should now be able to add the Value type field as a legend.

[Anonymous]

Right click in Query editor? Unpivot? I feel I've been told to use a left handed screwdriver...

I am trying to create a summarised table grouped by postcode which I can then use as a field (below Legend) for legend. It is value_A/Value_B.

Postcode	Value_A	Value_B	Legend
4500	10058	20000	0.5029
2300	20060	30090	0.666667

[suparnababu8]

Hi @Anonymous 

I think you have to divide your action into to ways. 

Step-1 --> Create a calculated table

SummaryTable = 
SUMMARIZE(table2,table2[Postcode],
    "Sum_Value_A", SUM(table2[Value_A]),
    "Sum_Value_B", SUM(table2[Value_B])
)

Step -2 --> Add a Calculated Column to the above table

SummaryTB = 
ADDCOLUMNS(SummaryTable,
    "LVR_MAP", DIVIDE([Sum_Value_A], [Sum_Value_B]))

Therfore, now you can use the LVR_MAP column as a legend in your map visual. Ensure that the Postcode is used in the location field and the LVR_MAP in the legend field.

Let me know if it works

[Anonymous]

Thanks for your reply - unfortunately not. The Addcolumns statement gets returned with an error stating that there are multiple columns being referred to, And that they cannot be converted to a scalaer value... I don't think it likes the divide in there...