Need help for Dynamic Column Total Percentage

[YudhaHartono]

Hi Everyone

I'm currently trying to replicate the Column Total Percentage feature on Power BI using Measure.

The reason I need a measure for that is because I want to utilize the Parameter to be able to switch between measures (Values, Average, and Mix%), so it will be easier for the users and loads the data faster.

But the problem is, the denominator is stuck on the same value, so if users use any filter on the filter pane, it will show incorrect result. Please see below the Screenshot and the DAX of the measure.

STYLE In Sales = 
VAR Channels = VALUES(Consolidation[Channel])

VAR Retail = CONTAINS(Channels, Consolidation[Channel], "Retail")
VAR Digital = CONTAINS(Channels, Consolidation[Channel], "Digital")
VAR Wholesale = CONTAINS(Channels, Consolidation[Channel], "Wholesale")

VAR RetailCount = 
    IF(
        Retail,
        CALCULATE(
            DISTINCTCOUNT('Product Table'[STYLE]),
            FILTER(
                Consolidation,
                Consolidation[Sales Period] = "Full Price Period" &&
                Consolidation[Sales_Type] = "Actual" &&
                Consolidation[Channel] = "Retail"
            ),
            FILTER(
                'Product Table',
                'Product Table'[COLLECTION_NUM] > 22
            )
        ),
        0
    )

VAR DigitalCount = 
    IF(
        Digital,
        CALCULATE(
            DISTINCTCOUNT('Product Table'[STYLE]),
            FILTER(
                Consolidation,
                Consolidation[Sales Period] = "Full Price Period" &&
                Consolidation[Sales_Type] = "Actual" &&
                Consolidation[Channel] = "Digital"
            ),
            FILTER(
                'Product Table',
                'Product Table'[COLLECTION_NUM] > 22
            )
        ),
        0
    )

VAR WholesaleCount = 
    IF(
        Wholesale,
        CALCULATE(
            DISTINCTCOUNT('Product Table'[STYLE]),
            FILTER(
                Consolidation,
                Consolidation[Sales Period] = "Full Price Period" &&
                Consolidation[Sales_Type] = "Actual" &&
                Consolidation[Channel] = "Wholesale"
            ),
            FILTER(
                'Product Table',
                'Product Table'[COLLECTION_NUM] > 22
            )
        ),
        0
    )

// VAR RetailTotal =
//     IF(
//         Retail,
//         CALCULATE(
//             CALCULATE(
//                 DISTINCTCOUNT('Product Table'[STYLE]),
//                 FILTER(
//                     Consolidation,
//                     Consolidation[Sales_Type] = "Actual" &&
//                     Consolidation[Sales Period] = "Full Price Period" &&
//                     Consolidation[COLLECTION_NUM] > 22 &&
//                     Consolidation[Channel] = "Retail"
//                 ),
//                 FILTER(
//                     'Product Table',
//                     'Product Table'[CATEGORY] <> BLANK() &&
//                     'Product Table'[COLLECTION_NUM] > 22
//                 )
//             ),
//             REMOVEFILTERS(Consolidation[Channel])
//         ),
//         0
//     )

// VAR DigitalTotal = 
//     IF(
//         Digital,
//         CALCULATE(
//             CALCULATE(
//                 DISTINCTCOUNT('Product Table'[STYLE]),
//                 FILTER(
//                     Consolidation,
//                     Consolidation[Sales_Type] = "Actual" &&
//                     Consolidation[Sales Period] = "Full Price Period" &&
//                     Consolidation[COLLECTION_NUM] > 22 &&
//                     Consolidation[Channel] = "Digital"
//                 ),
//                 FILTER(
//                     'Product Table',
//                     'Product Table'[CATEGORY] <> BLANK() &&
//                     'Product Table'[COLLECTION_NUM] > 22
//                 )
//             ),
//             REMOVEFILTERS(Consolidation[Channel])
//         ),
//         0
//     )

// VAR WholesaleTotal = 
//     IF(
//         Wholesale,
//         CALCULATE(
//             CALCULATE(
//                 DISTINCTCOUNT('Product Table'[STYLE]),
//                 FILTER(
//                     Consolidation,
//                     Consolidation[Sales_Type] = "Actual" &&
//                     Consolidation[Sales Period] = "Full Price Period" &&
//                     Consolidation[COLLECTION_NUM] > 22
//                 ),
//                 FILTER(
//                     'Product Table',
//                     'Product Table'[CATEGORY] <> BLANK() &&
//                     'Product Table'[COLLECTION_NUM] > 22
//                 )
//             ),
//             REMOVEFILTERS(Consolidation),
//             FILTER(
//                 VALUES(Consolidation[COLLECTION_NUM]),
//                 Consolidation[COLLECTION_NUM] > 22
//             )
//         ),
//         0
//     )

VAR ActualCount = 
    CALCULATE(
        DISTINCTCOUNT('Product Table'[STYLE]),
        FILTER(
            Consolidation,
            Consolidation[Sales_Type] = "Actual" &&
            Consolidation[Sales Period] = "Full Price Period"
        ),
        FILTER(
            'Product Table',
            'Product Table'[CATEGORY] <> BLANK() &&
            'Product Table'[COLLECTION_NUM] > 22
        )
    )

// VAR ColumnTotal = 
//     RetailTotal + DigitalTotal + WholesaleTotal

VAR ColumnTotal =
    CALCULATE(
        CALCULATE(
            DISTINCTCOUNT('Product Table'[STYLE]),
            FILTER(
                Consolidation,
                Consolidation[Sales_Type] = "Actual" &&
                Consolidation[Sales Period] = "Full Price Period" &&
                Consolidation[COLLECTION_NUM] > 22
            ),
            FILTER(
                'Product Table',
                'Product Table'[CATEGORY] <> BLANK() &&
                'Product Table'[COLLECTION_NUM] > 22
            )
        ),
        REMOVEFILTERS(Consolidation),
        FILTER(
            VALUES(Consolidation[COLLECTION_NUM]),
            Consolidation[COLLECTION_NUM] > 22
        )
    )


RETURN
    SWITCH(
        TRUE(),
        SELECTEDVALUE('Value Selections'[Category]) = "Value",
        ActualCount,

        SELECTEDVALUE('Value Selections'[Category]) = "Average",
        DIVIDE(
            [Actual_Sales],
            ActualCount
        ),
            ColumnTotal
    )

If there is any alternatives except the Bookmark, I'd love to know.

Thanks

[v-pgoloju]

Hi @YudhaHartono,

Just checking in to see if your issue has been resolved. If you’ve found a fix whether from the responses here or through your own troubleshooting it would be great if you could share it with the community.

This helps others facing similar challenges benefit from your experience.

Thanks again for your time and contribution!

Best,
Prasanna Kumar

[v-pgoloju]

Hi @YudhaHartono,

Thanks for reaching out to Microsoft fabric community.

As we haven’t received any further updates and there are no outstanding queries at the moment, we’ll go ahead and close this thread for now. If you have any additional questions in the future, please don’t hesitate to start a new thread we’re always here to help.

Warm regards,
Prasanna Kumar

[v-pgoloju]

Hi @YudhaHartono,

Thank you for reaching out to the Microsoft Fabric Forum Community. Also, thanks to @speedramps  for the prompt and helpful response, and for sharing valuable suggestions about the community.

Could you please share some sample data that we can use to better understand and assist with your issue?

Best regards,
Prasanna Kumar

[speedramps]

Hello again @YudhaHartono 

Your Product table has used the wrong datatype for SKUs.

Your Product table has duplicate rows for SKUs.
A Product Dimension should have a unqiue product id for each product, collection and style combination

You shoudl then use this product id in the fact table relationshios
 

Fact tables need a  product id to relate back to the product table
You Fact Table has SKU and Collection. Neither of these are unqiue

I see you are a new member.

I want to heklp you but posting your buggy dax and all your data with a vague explanation and expecting us to do your job for you, is not the purpose of this forum. 😀😀

Please follow the instructions we provided to write a clear question.

Ask you manager, a collegaue or teacher (if you are student) to help you write the question clearly. Thank you.

Simplify your problem, buy asking one question at a time with the minimal data.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). 

* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

[speedramps]

Please give access to your data.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click ”Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[YudhaHartono]

Oops, sorry about that

Try to access it from here

Consolidation.csv 

Product Table.csv 

[YudhaHartono]

Hi @v-pgoloju 

Sure thing

Let's say I have 2 tables called "Consolidation" and "Product Table"

These tables have a many-to-one type of relationship on the "SKU" column. Please see attached links for my sample data.

Consolidation.csv

Product Table.csv

What I'm trying to do is to find a dynamic denominator value for the Column Total Percentage, which I unfortunately don't know how to do it.

I wrote this DAX and it only returns a static value which is "176" for Collection 23 as you saw in the screenshot.

VAR ColumnTotal =
    CALCULATE(
        CALCULATE(
            DISTINCTCOUNT('Product Table'[STYLE]),
            FILTER(
                Consolidation,
                Consolidation[Sales_Type] = "Actual" &&
                Consolidation[Sales Period] = "Full Price Period" &&
                Consolidation[COLLECTION_NUM] > 22
            ),
            FILTER(
                'Product Table',
                'Product Table'[CATEGORY] <> BLANK() &&
                'Product Table'[COLLECTION_NUM] > 22
            )
        ),
        REMOVEFILTERS(Consolidation),
        FILTER(
            VALUES(Consolidation[COLLECTION_NUM]),
            Consolidation[COLLECTION_NUM] > 22
        )
    )

The number is correct in context of the whole collection.

But if the users only want to see specific category and use the filter pane, it doesn't change accordingly.

For example the Users filter on "Bags, Dress, Skirt"

I want it to do something like

Category	Denominator
Bags	6
Dress	6
Skirt	6

This number makes more sense because it is not possible to produce 176 of different style only for 3 kind of products

Hopefully it is more understandable

[speedramps]

I want to help you but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). 😀
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[v-pgoloju]

Hi @YudhaHartono,

Thank you for reaching out to the Microsoft Fabric Forum Community.

To help us understand your requirement more clearly, could you please share a sample dataset along with the expected outcome in text format (not as a screenshot)?

Best regards,
Prasanna Kumar