Forum Discussion

BaldAccountant's avatar
BaldAccountant
Helper III
3 years ago
Solved

Power pivot - Dynamic Measure

I am tyring to create a dynamic measure based on a post on another website in power pivot.  There are two elements: -a table that creates dynamic choice of row values (i.e. parse out by insurance ty...
  • tamerj1's avatar
    tamerj1
    3 years ago

    BaldAccountant 
    I've changed the data model relaying more on DAX. Please refer to attached sample file.

    =
VAR FilterTable =
    FILTER (
        Data,
        CONTAINS ( VALUES ( Groups[Group Values] ), Groups[Group Values], Data[FC] )
            || CONTAINS (
                VALUES ( Groups[Group Values] ),
                Groups[Group Values], Data[Specialty]
            )
    )
RETURN
    SWITCH (
        MIN ( Multi[MSR Number] ),
        1, DIVIDE ( CALCULATE ( [Cases], FilterTable ), [Cases] ),
        2, DIVIDE ( CALCULATE ( [Charges], FilterTable ), [Charges] )
    )
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi BaldAccountant 
My understanding:
1. 'Multi' is used to select between different measures.

2. Group is used for slicing and dicing.

3. Data2 is the main table with all the data.

4. No relationships.

Please correct me if I'm wrong.
My guess would be that ALLSELECTED ( Data2 ) is creating this issue. However, won't be able to provide a concerete solution without having the complete picture. Would you please share a screenshot of the Pivot Table blanking out any sensetive data but indicating which column in the pivot table belongs to which table?

BaldAccountant's avatar
BaldAccountant
Helper III
3 years ago

Here is a real basic example

Data

IDFCSpecialtyCharge

1Blue CrossHand100
2MCRHand200
3Blue CrossFoot300

Intermediary table 1

Fin Class2Fin Class3

Blue CrossBlue Cross
MCRMCR

Intermediary table 2

Spec2Spec3

FootFoot
HandHand

 

Groups

Group nameGroup ValuesFin ClassSpecialty

Fin ClassBlue CrossBlue CrossHand
Fin ClassBlue CrossBlue CrossFoot
Fin ClassMCRMCRHand
Fin ClassMCRMCRFoot
SpecialtyHandHandHand
SpecialtyHandHandFoot
SpecialtyFootFootHand
SpecialtyFootFoot

Foot

Measures table

MeasureMSR Number

Pct of Cases1
Pct of Charges2

Pct of Cases:=DIVIDE([Cases],CALCULATE([Cases],ALLSELECTED(Data)),0)

Pct of Charges:=DIVIDE([Charges],CALCULATE([Charges],ALLSELECTED(Data)),0)

Measure Calc:=switch(TRUE(),min(Multi[MSR Number])=1,[Pct of Cases],min(Multi[MSR Number])=2,[Pct of Charges])

Relationships - I was wrong - measures does not have a relationship

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

     BaldAccountant 
    Please try using ALL instead of ALLSELECTED

    Cases :=
DIVIDE ( [Cases], CALCULATE ( [Cases], ALL ( Data2 ) ), 0 )

    The SWITCH measure can be simplified (just less dax) as follows

    MeasureValue5 :=
SWITCH (
    MIN ( Multi[Msr Number] ),
    1, CALCULATE ( FORMAT ( [Current % Cases], "#,#0.0%;(#,#0.0%);-" ), Groups ),
    2, CALCULATE ( FORMAT ( [Avg Charges], "#,#0;(#,#0):-" ), Groups ),
    3, CALCULATE ( FORMAT ( [Cases], "#,#0;(#,#0;-" ), Groups ),
    4, CALCULATE ( FORMAT ( [Current % of Charges], "#,#0.0%;(#,#0.0%);-" ), Groups ),
    5,
        CALCULATE (
            FORMAT ( [Expense % Reimbursement], "#,#0.0%;(#,#0.0%);-" ),
            Groups
        )
)
    • BaldAccountant's avatar
      BaldAccountant
      Helper III
      3 years ago

      Sorry, I tried your solution using ALL instead ALLSELECTED, but it gave the same result, all 100%

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        BaldAccountant 

        Any possibility you can share a sample file?