I want to see the average percent of two columns

[Anonymous]

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?

[Anonymous]

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

[Greg_Deckler]

Sould be sooooo much easier with sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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

[Anonymous]

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]

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

[Anonymous]

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]

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

Will this help maybe?

[Anonymous]

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]

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

[Anonymous]

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]

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")