Mixed Data column

[Bluesfan1972]

Have been looking for a way to create a 'mixed data' column, as requested by the customer, but have yet to find a solution that works.  The customer is asking for a colum that shows percent of total for the rows, but shows the 'count number' as the total.  It would look like this:

Getting the % of column is easy but getting the total to show the sum of visits and not 100% is where I am stuck.

Is this possible in PowerBI?

[Anonymous]

Hi @Bluesfan1972 ,

Is there any progress on this issue?

If an answer has been helpful, please consider accepting the answer to help increase visibility of this question for other members of the Fabric community.

If not, please let us know what is still needed in the comments so the question can be answered. Thank you for helping to improve Fabric community!

[vicky_ · Accepted solution]

Yeah - the trick is to use HASONEVALUE(Table[Total Visits]) or HASONEFILTER(Table[Total Visits]) or ISINSCOPE() in order to identify the totals column - you can see this page for more details: https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/

Next is to update your DAX, which you can do in a pattern like:

Measure = IF(HASONEVALUE(Table[Visits]), 
    // your code for calculating the percentage here,
    // your code for the total here
)

Ideally, you would keep that as a number, and then use the dynamic format strings to show those number(s) as a percentage in the values, and then a number in your totals. You can use something like:

IF(HASONEVALUE(Table[Visits]), "#0.00%", "#########")

Read more about format strings here: https://www.sqlbi.com/articles/introducing-dynamic-format-strings-for-dax-measures/.

Hope this helps.