MS Excel / VBA help thread

I still refuse to use Xlookup, Index double match all the way.

Also the way excel does the count\average\min\max if(s) is weird and slightly annoying.

for a single criteria, it is criteria range, criteria, sum range

for multiple criteria it is sum range, criteria range 1, criteria 1, criteria range 2, criteria 2 etc.

But you can use sumifs with a single criteria, so countif is superflous, which is guess why there is no Maxif but there is a maxifs.

I've just spent the best part of at least 2 days wondering why my quintiles don't display correctly in BI.
ONLY TO FUCKING REALISE NOW THAT BI WAS SUMMING THE QUINTILE. THANKS A FUCKING LOT YEAH.

Can a pivot table display text in the main body of it?

eg ColA and ColB are going to be the rows and columns and ColC contains a variety of text

A very cursory attempt just now suggested it wasnt straightforward

Design
Tabular view
Repeat all items
No subtotal

Put the three columns as columns

Try that?

thanks

didnt work for me. so i cheated.

I know this isn't the right thread, but I don't think there is a "data science" thread.

I'm trying to do CFA in Python, I'm using Factor Analyzer to define my model, but I need to define residual correlations as well as my measurement model, and Factor Analyzer doesn't seem to be able to let me do that. Does anyone have any other suggestions?

I know I can use Lavaan in R, my behavioural scientist colleague has created that, but the dev I'm working with and my CTO are very tentative about trying to run R "in the cloud" and industrialise it in that way, so are keen for a Python solution instead.

Doesn't Scikitlearn do factor analysis too?

It does, but as far as I can see, it's exploratory rather than confirmatory.

Got me, I'm afraid.

Short of coding something yourself based on the R implementation...

A PowerBI formatting question. I've got a scatter chart with a fair few data points on there. The points are multi-coloured but the category labels are pretty congested so it's hard to tell which label relates to which point. I'd like the labels to be in the same colour as the points rather than black to make it easier to tie them up.

This is an example of the kind of thing, how do I make the colour of the player names match the circles? Cheers

1 Attachment

tables and names

I know it’s a relatively old comment but there’s never a bad time for this. I wasted a couple hours last week hunting an error in my code, only to realise my agency-side colleague hadn’t updated the rows reference for a bunch of pivot tables built off my output. A couple years ago I started forcing myself to always make table before pivot, even if I don’t think I will reuse the file down the line.

it really helps out later on...

Are the category colours auto generated?
otherwise it could be the same sort of methodology as a date table?
so you build a table of all the player names, write a formula to assign a colour to their name in another field. then link by a relationship?
is that making sense?

Auto generated (although they don't have to be).
I just can't see an option to assign more than one colour to the text, not sure if I'm missing something. I can'#t find anyone asking the question online which seems surprising so not sure if I'm using the wrong terms.

https://chandoo.org/wp/tag/conditional-formatting-power-bi/
https://chandoo.org/wp/conditionally-format-visuals-in-power-bi/

I would create a player name table and then assign a colour code.
Or I'd assign it as a column in the data table.
Then when you add it to the viz it should be possible to use in the legend?

Cheers. I think the issue is I'm not trying to do it in the legend, they need to be set as Category Labels to appear next to the circles (bringing the names in as the legend they just all show up at the top with a colour key) but there isn't an option anywhere (that I can find) to change colours for those (other than for all labels).

I'm guessing you've tried plotting them as a second set of data with no markers and just text?
Fuck knows how that would look though

I don't think I'm able to do that for that style of chart, got to use one of the preset shapes.

https://docs.microsoft.com/en-us/power-bi/developer/visuals/develop-circle-card

i think you are off here

Cheers, looks like it's possible but a lot of work. I'll shelve it for the moment but good to know that there's this option.

I'm trying to create running totals in power bi.
It's fairly easy to cheat using quick measures.

I have a table with multiple fields. If I want to create a running total for one field it's easy to cheat and use a quick measure:

Sum of running total in Date =
CALCULATE(

SUM('table'[value]),
FILTER(
    ALLSELECTED('table'[Date]),
    ISONORAFTER('table'[Date], MAX('table'[Date]), DESC)
)

Which creates a running total of value based on the date. I can use filters / slicers on the page to filter by the other fields (for example [Location]). But what I want to know is:

How can I create either a measure (dax) or a column in the table (m/powerquery) to create a running total for each location on a date.

So something like

ALLSELECTED('table'[Date]),'table'[Location]),
I would have thought would work but hasn't yet.

I found this: https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query

But I couldn't get it to work on more than two groupings.

But ultimately, what I'd like to do is create running totals for multiple fields (i.e. more than date + location, because there are people of certain ages in the location at certain times).

So I'd like to be able to say: on 08 04 2021 there were a total of 74 men who'd been to place x, while on 09 04 2021 there were 78 men at location x. Make sense?

|Date |Location |Sex |Value|

What's the best way to use Excel for calendar and time stuff? (The correct answer isn't "don't"). I want to set it up and never see it again.

A team of 30 people need to say what they worked each day.
Obviously, the thing people want is a matrix of names down the side, date along the top, shift they worked written in words.
This is easily read but not so easily calculated from.

I'd like to set out
Date time on time off name

And then a nice long table, which can be easily pivoted to show a calendar and the number of hours worked a day.

But I know that won't work, because the people can't get their head around it.
What else can I do?

Use the matrix and then have a helper sheet which formats it nicely for working on.

They write things like
9 to 4
And
10-8
And
10-2

I think I'm going to cry