Forum Discussion

digialternative's avatar
digialternative
Frequent Visitor
3 years ago

Measure with switch not showing result

Hello Experts,

 

Some question, I am building a financial balans.
For every item in my Balans table I have a measure to calculate the value's, such as: 

debiteuren =
CALCULATE(
    [Balans SUM],
    'Balans_Rapport'[Level 1] = "debiteuren",
    FILTER(
        ALL(Datum),
        Datum[Datum] <= MAX(Datum[Datum])
    )
)

 

and this works great.

Via Switch function, I place all these measures in the matrix at the correct postion:

 

Balans position items Level 1 =
SWITCH(
    SELECTEDVALUE('Balans_Rapport'[Level 1]),
    "Activa", [Activa],
    "Voorraden", [Voorraden],
    "Onderhanden projecten", [Onderhanden Projecten],
    "debiteuren", [debiteuren],
    "vorderingen op groepsmaatschappijen", [vorderingen op groepsmaatschappijen],
    "vordering belastingen en sociale verzekeringen", [vordering belastingen en sociale verzekeringen],
    "ovg vorderingen en overlopende activa", [ovg vorderingen en overlopende activa],
    "liquide middelen", [liquide middelen],
    "totaal debet", 1
)
 
In the above example, I get the correct table layout, correct values for all the fields, with the exception of the totaal (total) debet field. 
I get the correct value for total debet when I use it as a separate column via the following measure:
 
totaal debet =
CALCULATE([Activa] + [Voorraden] + [Onderhanden Projecten] + [debiteuren] + [vorderingen op groepsmaatschappijen] + [vordering belastingen en sociale verzekeringen] + [ovg vorderingen en overlopende activa] + [liquide middelen])
 
However, when I use this [totaal debet] field in the switch function (at the place where not the 1 is located), I get a blank result. 
When I put in the 1, the 1 shows up as correct value 1 at the correct place.
 
So it seems my measures are all fine, but how come that I cannot get a value displayed?

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi digialternative ,

    Please create a measure based on the following :

    Balans position items Level 1 =
SWITCH(
    SELECTEDVALUE('Balans_Rapport'[Level 1]),
    "Activa", [Activa],
    "Voorraden", [Voorraden],
    "Onderhanden projecten", [Onderhanden Projecten],
    "debiteuren", [debiteuren],
    "vorderingen op groepsmaatschappijen", [vorderingen op groepsmaatschappijen],
    "vordering belastingen en sociale verzekeringen", [vordering belastingen en sociale verzekeringen],
    "ovg vorderingen en overlopende activa", [ovg vorderingen en overlopende activa],
    "liquide middelen", [liquide middelen],
    "totaal debet", 1
)
    result =
VAR _b =
    SUMMARIZE ( 'table', 'table'[level 1], "aaa", [Balans position items Level 1] )
RETURN
    IF (
        ISINSCOPE ( 'table'[level 1] ),
        [Balans position items Level 1],
        SUMX ( _b, [aaa] )
    )

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

    • digialternative's avatar
      digialternative
      Frequent Visitor
      3 years ago

      Hello Rongtie,

      Thanks for your answer.
      Here I have attached a sample that will show you my challenge.

      excel 2 simple tables 

      pbix file 

       

      I have 2 tabs in excel, first for the layout of the page (Level 1).
      2nd for the values of A...H, via a measure.

      Now what I want is to have the SUM of A:D = 100, which is calculated nicely by your solution, but I cannot get it to display at the Level 1 "SUM(A:D)". Same goed for E:H, via your formula the total is shown to be 540, but how to show this value at Level 1 "SUM(E:H). And Later I need to subtract A:D - E:H (100 - 540 ==> 440) to be displayed at DIFF (SUM(A:D)-(E:H)) field.

       

      I hope this makes my problem clear.

      Thanks