Forum Discussion

Attrywa's avatar
Attrywa
Frequent Visitor
2 years ago

Grand total with blank

Hi, 


I have a matrix table with current conversion and conversion Y-1 based on ISO years and weeks, which are selected on the filter pane by the user of the report: 

The problem is that grand total for my Y-1 conversion measure is blank. 


Measure for Y-1 conversion:
Conversion Y-1 =

VAR CurrentWeek =  VALUES ( Date[Week ISO] )
VAR CurrentYear =   SELECTEDVALUE ( Date[Year ISO] )  
VAR a =
        CALCULATE (
            [Current conversion],
            FILTER (
                ALL ( Date ),
                Date[Year ISO] IN {CurrentYear-1} && Date[Week ISO] IN CurrentWeek )
            )
   RETURN   a
 
Could you please help me to rewrite the measure, or modify it so the grand total is caculated properly? 
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Attrywa , You can try a measure like

    week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

     

    of switch your measure with this measure in grand total

    if(isinscope([Year ISO]), [Conversion Y-1], [Week Behind sales] )

     

    Of you can Witch GT with this Measure

     

    Conversion Y-1 GT =

    VAR CurrentWeek =  Maxx(allselected(Date),  Date[Week ISO] )
    VAR CurrentYear = Maxx(allselected(Date),  Date[Year ISO] )  
    VAR a =
            CALCULATE (
                [Current conversion],
                FILTER (
                    ALL ( Date ),
                    Date[Year ISO] IN {CurrentYear-1} && Date[Week ISO] IN CurrentWeek )
                )
       RETURN   a
     
    How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA
    • Attrywa's avatar
      Attrywa
      Frequent Visitor
      2 years ago

      amitchandak  Thank you for quick answer. 
      I can't use Dateadd function set on a day, because the number of days in each year is not constant - in 2022 and 2023 there are 365 day, however in 2024 there are 366 days according to my date table.
      Furthermore Dateadd function set on a year is also not correct, due to the fact that W02 of 2024 is 07.01 - 13.01 and W02 in 2023 is 08.01-14.01.

      I have used the second option - Conversion Y-1 GT, but the result is the value from the last week of the previous year: 

      And what I would need in GT is the sum of equivalent X and Y from the W01-W04 in 2023 + W51-W52 in 2022 divided by one another