Manually iterating over combos that don't exist in my original table

[nmatthe2]

Hello!

I have created a variable called ReallocateClassesPerDay. On some days different colleges do not have any classes, however, I can still calculate how many classes need to be reallocated each day. When I try to total all of the classes that need to be reallocated, this measure does not include calculations where colleges do not have any classes scheduled (the pair acad_org x day) does not exist. How can I still include the values for ReallocateClassesPerDay for these combos that don't exist in my original table (Sheet1)

TotalReallocation =

SUMX(

    FILTER(

        SUMMARIZE(

            Sheet1,

            Sheet1[acad_org],

            Sheet1[day]

        )

    ),

    CALCULATE([ReallocateClassesPerDay])

)

*Here is ReallocateClassesPerDay for reference :

ReallocateClassesPerDay =

VAR MinT = SELECTEDVALUE(MinThreshold[MinThreshold])

VAR MaxT = SELECTEDVALUE(MaxThreshold[MaxThreshold])

VAR TotalClasses =

    CALCULATE(

        SUM(Sheet1[frequency]),

        ALLEXCEPT(Sheet1, Sheet1[acad_org])

    )

VAR ClassesOnThisDay =

    COALESCE(SUM(Sheet1[frequency]), 0)

VAR PctOnThisDay = DIVIDE(ClassesOnThisDay, TotalClasses)

VAR MinRequired = TotalClasses * MinT

VAR MaxAllowed = TotalClasses * MaxT

VAR RawReallocate =

    SWITCH(

        TRUE(),

        PctOnThisDay < MinT, MinRequired - ClassesOnThisDay,

        PctOnThisDay > MaxT, ClassesOnThisDay - MaxAllowed,

        0

    )

RETURN

    CEILING(RawReallocate, 1)

[v-venuppu]

Hi @nmatthe2 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @nmatthe2 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @nmatthe2 ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu · Accepted solution]

Hi @nmatthe2 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @FBergamaschi for the prompt response.

I have tried replicating the scenario using sample data.Please go through the attached PBIX file for your reference.

Thank you.

[FBergamaschi]

Please include a small rows subset (in a usable format, not an image) of all the tables involved in your request, so that we can import them in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Without the above, it is impossible to assist you

Need help uploading data? click here

Want faster answers? click here