cancel
Showing results for
Did you mean:
Anonymous
Not applicable

I want to see the average percent of two columns

Hello!

I've tried asked for help before but still havent solved it. I bet its really simple, but i'm new on this and its hard.

I got these two columns:

``MSP/HSP 1tim = IF('logg'[Time]>1/24/60*60;"Missed";"Completed")``

and

``LSP 2tim = IF('Logg'[Time]>1/24/60*120;"Missed";"Completed")``

I want to merge these two columns together and see the average percent of how many "Missed" and "Completed" there is. I cant find a way to do this. Can someone please help me?

10 REPLIES 10
Anonymous
Not applicable
What do you mean by "merge"? If these 2 cols are in the same table (and they are), then how can they be "merged"?

By the way, 1/24/60*60 = 1/24, and 1/24/60*120 = 1/12.

From this one can conclude, that if [LSP 2tim] = "Missed", then the other one is "Missed" as well. But the "Completed" entries for the [LSP 2tim] can be split into "Missed" and "Completed".

So, what's the rule for a merger?

Best
D
Super User

But, perhaps something like:

``````Measure Percent Missed =
VAR __Table =
UNION(
SELECTCOLUMNS('Table',"Value",[MSP/HSP 1tim]),
SELECTCOLUMNS('Table',"Value",[LSP 2tim])
)
VAR __Count = COUNTROWS(__Table)
VAR __Missed = COUNTROWS(FILTER(__Table,[Value] = "Missed"))
RETURN
__Missed / __Count``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

The Table looks kinda like this. If MSP/HSP Inställelsetid 1tim its <1hour it says "Avklarat" (Completed) and if its >1hour it says "Missat" (Missed). And the same goes with "LSP inställelsetid 2tim".

I want to see the average percent of how much is "Avklarat" (completed) and "Missat (missed) from both "MSP/HSP inställelsetid" 1tim and "LSP inställelsetid 2 tim" in a new column.

Anonymous
Not applicable

Can someone try to help me? I really need help with this

Anonymous
Not applicable
Sorry, your requirements are not really understandable. Can you please craft a little table and show exactly what you mean by:

the average percent of how much is "Avklarat" (completed) and "Missat (missed) from both "MSP/HSP inställelsetid" 1tim and "LSP inställelsetid 2 tim" in a new column.

I can't understand what all this means. Please make a small table with the calculations and the result.

Thanks.

Best
D
Anonymous
Not applicable

I dont know how to make a table and explain better there sorry.. I will try my best explaining

Will this help maybe?

Anonymous
Not applicable

Hi. First of all, you have to have measures for what you want to do. You can't achieve what you want by just dropping fields into your visuals. Secondly, you should create a good, star-schema model to be able to do things correctly. I attach a file that shows you how to do what you want and it also highlights the most important bit: how to model data. Fact tables should only contain facts and keys to dimensions. All their columns must be hidden. Only measures are allowed to be visible. Slicing is done through dimensions only.

If you decide to by-pass the rules, you'll be suffering a lot. GUARANTEED.

There are 2 different 'averaging' measures in the model. For the data in there right now they give the same answer but that's a coincidence. If you include more dimensions and start slicing by them, the figures returned by them will differ and hence the pie charts will differ as well. So, please be careful with this. Just don't assume - test.

Best

D

Anonymous
Not applicable

So I shouldnt use those formulas that I have? Because it might not be correct after some time? I should only use measures ?

Anonymous
Not applicable
You should ALWAYS use DAX measures for aggregation. Fact tables should never, ever be exposed. Forget about any ad-hoc measures created by PowerBI automatically. It only leads to errors and frustration.

By the way, have you checked the file I attached to my latest post?

Best
D
Anonymous
Not applicable

Im sorry but I have no idea how to give you sample data, its the data I wrote from the beginning, those 2 columns.. I've tried so much in several days now to get this fixed. Starting to give up... I cant simple give out the pbix file here, its too much sensitive info

I got those two columns that are based on this:

``Time = IF('Logg'[hour]<24&&'Loggt'[Minute]<60; [Hour] & ":" & [Minute] & ":" & [Seconds];"23:59:59")``