Total not adjusting with breakdown of categories

[justmakingit]

Hey Everyone,

For my project I need to show total unique ID's overall, unique ID's per Media Type (Dashboard, Article, Video) and then Unique ID's per media type breakdown (Dashboard, Article 1, Article 2, Video 1, Video 2). I also need to show the % of the grand total for each breakdown as well. The issue is when I'm creating my tables no matter the number of Unique ID's per Media Type it is calculating the grand total and % of grand total as the original 146 unique ID's I have. For example. For the Dashboard I have 137 Unique ID's, the Videos 5 Unique IDs, and the Articles 15 Unique ID's. Which all add up to 157. However, the total and % is only accounting for the 146 overall. It is not accoutning for the extra breakdown of categories. Does this make sense? I cannot figure it out and its causing me to pull my hair out. 

[SamInogic]

Hi,

We are a bit unclear about your exact requirements. However, it seems like you are looking to calculate the count of records based on a few filters.

Below is the sample data we have used to fulfill your request:

Unique ID	Media Type	Breakdown
1	Dashboard	Dashboard
2	Dashboard	Dashboard
3	Dashboard	Dashboard
4	Article	Article 1
5	Article	Article 1
6	Article	Article 2
3	Article	Article 2
7	Video	Video 1
8	Video	Video 1
9	Video	Video 2
10	Video	Video 2
11	Dashboard	Dashboard
6	Article	Article 2
12	Video	Video 1
13	Dashboard	Dashboard

Create DAX Measures Respectively:

Overall Unique IDs:

Overall Unique IDs = DISTINCTCOUNT('Table'[Unique ID])

Unique IDs per Media Type:

Unique IDs per Media Type =

CALCULATE(

    DISTINCTCOUNT('Table'[Unique ID]),

    REMOVEFILTERS('Table'[Breakdown])

)

Unique IDs per Breakdown:

Unique IDs per Breakdown = DISTINCTCOUNT('Table'[Unique ID])

% of Grand Total Media Type:

% of Grand Total Media Type =

DIVIDE(

    [Unique IDs per Media Type],

    [Overall Unique IDs],

    0

)

% of Grand Total Breakdown:

% of Grand Total Breakdown =

DIVIDE(

    [Unique IDs per Breakdown],

    [Overall Unique IDs],

    0

)

By using these measures, you should be able to achieve the desired result.

Please refer to the screenshot provided below for further clarity.

Thanks

[Anonymous · Accepted solution]

Hi @justmakingit ,

Do you mean that your total unique ID's overall is displayed as the non-repeat number of all ids, rather than the non-repeat number of id by type?

For example, the total unique ID's overall should be 4 rather than 3.

If I understand you correctly, you can create measure to show the total unique ID's overall calculated by category.

Measure = SUMX(DISTINCT('Table'[Category]),CALCULATE(DISTINCTCOUNT('Table'[ID])))

Best regards,

Mengmeng Li

[justmakingit]

This is exactly what I was looking for, thank you so much!!!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...