Issue with correct output

[smorph]

Hey everyone,

I use a Forms survey which I like to "analyze" with Power Bi.
I already found an option to be able to compare the answers together.

Goal from my side is to reccomend the next steps to end user of that report about the next steps which they should go.

As you can see, I'm using three visuals with a dedicated colum to calculate the sum of all given answers to a specific topic, in this case: daily business, change and project.

In the visual below should be getting up the recommendation.
In the specific case you see now there should be shown up "daily business", but instead i get "change". Please find the code below.

The colums I'm using are: 1 Tagesgeschäft  (daily business) (left visual); 2 Change (middle visual); 3 Projekt (right visual).

    SWITCH(
        TRUE(),
        //Prüfung ob Tagesgeschäft
        'Projektwürdigkeitsanalyse'[1 Tagesgeschäft] > 'Projektwürdigkeitsanalyse'[2 Change] && 'Projektwürdigkeitsanalyse'[1 Tagesgeschäft] > 'Projektwürdigkeitsanalyse'[3 Projekt], "Tagesgeschäft",

        //Prüfung ob Chaage
        'Projektwürdigkeitsanalyse'[2 Change] > 'Projektwürdigkeitsanalyse'[1 Tagesgeschäft] && 'Projektwürdigkeitsanalyse'[2 Change] > 'Projektwürdigkeitsanalyse'[3 Projekt], "Change",

        //Prüfung ob Projekt
        'Projektwürdigkeitsanalyse'[3 Projekt] > 'Projektwürdigkeitsanalyse'[1 Tagesgeschäft] && 'Projektwürdigkeitsanalyse'[3 Projekt] > 'Projektwürdigkeitsanalyse'[2 Change], "Projekt",

        //Ausgabe wenn nicht zutreffend
        "nicht eindeutig")

What I try to achive is that, if the column "1 Tagesgeschäft" is bigger than the other two then the below visual should return "Tagesgeschäft", but instead I get "Change" returned.

I already tried to put SUM() around each column, with no effect.

I already checked with other entries and when only one of the three colums is bigger than 0 and the other ones are exactly 0 it works fine.

Right now I'm not able to find the issue...

Please the code of the three columns:

1 Tagesgeschäft = 
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Bereiche] = "= 1", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Personen] = "<= 3", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Dauer] = "wiederkehrend", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Investitionshöhe] ="<= 10.000 €", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Inhaltliche Komplexität] = "gering", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Neuartigkeit] = "gering", 2, 0) +
    IF('Projektwürdigkeitsanalyse'[Risiken] = "gering", 1, 0)

2 Change = 
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Bereiche] = "<= 3", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Personen] = "<= 5", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Dauer] = "<= 3 Monate", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Investitionshöhe] ="< 50.000 €", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Inhaltliche Komplexität] = "mittel", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Neuartigkeit] = "mittel", 2, 0) +
    IF('Projektwürdigkeitsanalyse'[Risiken] = "mittel", 1, 0)

3 Projekt = 
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Bereiche] = "> 3", 3, 0) + 
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Personen] = "> 5", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Dauer] = "> 3 Monate", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Investitionshöhe] =">= 50.000 €", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Inhaltliche Komplexität] = "hoch", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Neuartigkeit] = "hoch", 2, 0) +
    IF('Projektwürdigkeitsanalyse'[Risiken] = "hoch", 1, 0)

Thanks in advance.

[smorph]

Hey @v-yadongf-msft,

thanks for getting back to me.

1. This one is the data from the forms excelfile which will be pushed into datase.
   
2. This one is the expected result built in an other excel file (that was the basic idea)
   
   
   

The 2nd pic shows you the initial idea of the evaluation which is now built with forms and the report.

Whats happens there:

By choosing one of the three options for each row (last colum), there will be created a total sum for each of the three columns (colums: 3, 6 and 9). Where the red box is, the sums will be compared and the biggest one will give back one of the three options: "Tagesgeschäft (daily business)", "Change" or "Project".

On report level I'm following the same idea by creating a sum for each of the answers (my last code block from initial post), that works very well and is double checked with that excel file.

My idea is to get the red box (screenshot) into a visual (first screen, 4th visual, initial post), by comparing the sums, like in the excel. For only one answer that will work very well, if I got more than one, its a total mess.

What I already found out is that the comparision of the sums works fine on each answer themselves but not in total over all answers.

Please let me know if you need something else.

Best regards,
Steffen

[v-yadongf-msft]

Hi @smorph ,

In order to better understanding your demands and give the right solution, could you please share with me some screenshots of your data after hiding sensitive information and tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.