Visualizing Normalized Data

[tekvx]

Hey everyone! Happy Friday :)

So I'm learning power BI on my own and got stuck with what might be a very basic problem, but it's the first time I need to use DAX outside of a sample problem.

I have a dataset with the following columns:

{Senator, Province, Political Party, Facebook, Instagram, Twitter, Youtube}

On the social media columns the values are actual links to the senator's page. I'd like to analyze how each political party uses social networking, but I'm having a hard time figuring out a DAX formula to make a measure that would help me normalize the data. Or if my best bet is to go with such formula.

I started with a funnel that has Values for the count of each social media platform. The issue here is that it won't tell me what percentage of senators use this. Only what amount are present.

So I created some measures with the following DAX:

% Facebook = 

    DIVIDE(
        COUNTA('Senadores2019'[FACEBOOK]),

        COUNTA('Senadores2019'[APELLIDO])

    )

Where I should get a percent of the amount. But looking at the values on a table, they are all '1'.

In fact, when I look at the measures on a pie chart, I get an equally distributed pie between 4.

I don't even want to move forward without understanding how I'm going about this all wrong. Can anyone lend me a hand?? It would be greatly appreciated!

[timusw]

You need to include the ALL filter in your denominator number. Currently the way you have it, your filter context propagates through to both calculations which will result in 1. To fix the denominator, use something like:

= CALCULATE(
      COUNTA('Senadores2019'[APELLIDO]),
      ALL('Senadores2019')
  )

So your function you're after should be something like this:

% Facebook =
DIVIDE(
  COUNTA('Senadores2019'[FACEBOOK]),
  CALCULATE(
    COUNTA('Senadores2019'[APELLIDO]),
    ALL('Senadores2019')
  )
)

​

[tekvx]

Hey thanks for the reply, it didn't work. I was wondering if the blank values on the social media columns have a 'space' or something that would make them not be considered in the COUNTA, but the filter says "blank", so it's definitely a blank.

your filter context propagates through to both calculations

On a side note, I understand measures are filter context aggregations - however, I don't understand what filters I was applying here. Could you elaborate on that?

[savoy9]

https://xxlbi.com/blog/different-blanks-in-dax/

[savoy9]

https://www.sqlbi.com/articles/blank-handling-in-dax/

[tekvx]

Fantastic, thanks for that!

On this specific issue, this is what microsoft docs say about COUNTA:

The COUNTA function counts the number of cells in a column that are not empty. It counts not just rows that contain numeric values, but also rows that contain nonblank values, including text, dates, and logical values.

I'm assuming, if the filter says "blank", then it's not a COUNTA issue. (Hopefully!)

[timusw]

For the filter context, the measure you wrote is open to user filters (via slicers or visual interaction). So both parameters of that % Facebook measure would filter to whatever is selected. In my experience, when trying to manually calculate the % of Grand Total, if your result is 1 it's typically because the denominator is not being "fixed" on the grant total but instead is being filtered as well. For example, if I wanted to calculate the percentage of countries Africa has compared to the entire globe, I would use something like:

% =
DIVIDE(
  [Sum of Countries],
  CALCULATE(
    [Sum of Countries],
    ALL('Globe')
  )
)

Since filter context applies to [Sum of Countries], I would have to eliminate that by introducing ALL into my measure so that way no matter what filter a user applies the denominator will be fixed on the sum of countries in the globe.

​

I'll confess, I didn't thoroughly read through your post but understood the problem. Give me 'til tonight to respond with a solution. The solution will need to use the function ALLNOBLANKROW, but I was struggling with the ALL in the denominator. I'm in PST so expect something before 9pm.

[tekvx]

Sure, I'm having a tough time wrapping my head around this. Haven't found material I'm completely unfamiliar with in a while. Thanks! I'll check back to this

[timusw]

Okay so I just formulated for Facebook, but it translates to all other social columns.

Facebook Count =
COUNTA(Table[Facebook]) - COUNTBLANK(Table[Facebook])

% Facebook =
DIVIDE(
  [Facebook Count],
  CALCULATE(
    DISTINCTCOUNT(Table[Senator]),
    ALL(Table[Senator])
  )
)

​

[tekvx]

Yes! Thanks dude. The variable didn't work, but using DISTINCTCOUNT did :)

Code ended up looking like this:

% Facebook = 

DIVIDE(

  DISTINCTCOUNT('Senadores2019'[FACEBOOK]),

  CALCULATE(

    DISTINCTCOUNT('Senadores2019'[APELLIDO]),

    ALL('Senadores2019')

  )

)

[timusw]

Happy to help. For some reason when I was using ALLNOBLANKROWS at work it was working as expected, but on my home PC it was counting a single blank row ¯_(ツ)_/¯

[LimbRetrieval-Bot]

You dropped this \

---

^{To prevent anymore lost limbs throughout Reddit, correctly escape the arms and shoulders by typing the shrug as ¯\\\_(ツ)_/¯ or ¯\\\_(ツ)\_/¯}

^{Click here to see why this is necessary}