Why result is wrongly appear in some reporting view when slicer select a value in dropdown

[Anonymous]

Hi Power BI  team,
I have some problem of my DAX formula and hope you can shed some light.

BACKGROUND: 
When I select all the value in Sector slicer, all the reporting view show the correct calculation. Meaning the pie chart shown the breakdown of the status during the past 12 months correctly, the bar shown the breakdown of the 172 status fall across individual month correctly, the individual sectors percentage shown the correct figure (i.e. the 172 status sit in each of he sectors, finally the two detail traction tables showing the right figure and number of rows. So everything is fine as of now. But, .......

PROBLEMS:

But when I select a particular sector value in the Sector slicer, for example, I select "Resources" sector, there will be two problems arised:

1.  Problem 1 - The sector percentage shown (see "Q1" yellow highlight) for all other sectors (except Resource) should be 0% (because I didn't select them) but they all have wrong value.  
2.  Problem 2 - The table detailed transaction records showing the wrong number of records as it should be showing 7 records of Target Machine Status of all of the 7 records should be "Assessed". Instead the table showing MORE than 7 record having all different type of Target machine status (see "Q2").

Note: if I make 2 selections or 3 selections in the Sector slicer, I will hits the Problem 1 and Problem 2. But if select all the value in Sector slicer, no problems like that will happen. Please scroll down to see my Dax code.

MY DAX FORMULA:

All the individual Sector % calculation has it own Measure (coding is similar in each sector object) and divided by a denominator Measure.

<Denominator> - 

* MeasureTotal = Calculate(Countrows('Asssessment_Action_20190219'),

                                          DATESINPERIOD('Asssessment_Action_20190219'[Last Assessment Date1].[Date], TODAY(),-13,MONTH)

                                          )

<Nominator> - Each of the sector value divided by the MeasureTotal.

* MeasureTransport = Calculate(Countrows('Asssessment_Action_20190219'),

                                                  'Asssessment_Action_20190219'[Sector]="TRANSPORT",

                                                   DATESINPERIOD('Asssessment_Action_20190219'[Last Assessment Date1].[Date], TODAY(),                                                                                                                                                                                                   -13,MONTH),

                                                   ALLExcept('Asssessment_Action_20190219','Asssessment_Action_20190219'[Sector]))/               

                                                   [MeasureTotal]

Hope you can let me know what are going wrong with Problem 1 (DAX related) and Problem 2 (which I just drag the fields into the table, but it seems not correspond to the slicer).

Thanks.

Tuff

[v-yuta-msft · Accepted solution]

@Anonymous ,

Generally, you may add a FILTER() statement in the Nomibator measure and check if this issue persists:

MeasureTransport =
CALCULATE (
    COUNTROWS ( 'Asssessment_Action_20190219' ),
    DATESINPERIOD (
        'Asssessment_Action_20190219'[Last Assessment Date1].[Date],
        TODAY (),
        -13,
        MONTH
    ),
    FILTER (
        ALLEXCEPT (
            'Asssessment_Action_20190219',
            'Asssessment_Action_20190219'[Sector]
        ),
        'Asssessment_Action_20190219'[Sector] = "TRANSPORT"
    )
) / [MeasureTotal]

Community Support Team _ Jimmy Tao

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