Create rows based on existing rows for a dimension of a visual

[sam_gift]

I am new in Power BI. I have the below requirement. There are 2 tables: 1 fact and 1 dimension.

Fact Table:

Currency	rep_item_id
2	1
2	1
4	1
5	1
6	2
22	2
85	3
67	3
28	3

Dimension Table:

rep_item_id	Description
1	KF001
2	KF002
3	KF003
4	KF004
5	KF005
6	KF006

I have to create measures for

KF004=KF003/KF002 i.e., (sum of currencies of rep_item_id=3)/(sum of currencies of rep_item_id =2)
KF005=KF001*KF002 i.e., (sum of currencies of rep_item_id=1) multiplied with (sum of currencies of rep_item_id =2)
KF006= if KF003>0 then 1-(KF003/KF001) else 0.i.e., if sum of currencies of rep_item_id is greater than 0, then (1 - (sum of currencies of rep_item_id=3)/(sum of currencies of rep_item_id =1) else 0.
Power BI Visual for report:

KF001	13
KF002	28
KF003	180
KF004	6.428571
KF005	364
KF006	-12.8462

This is the sample dataset. Please need assistance of how to implement this. The source is SAP. we are trying to move the reports from SAC Model to Power BI

[Anonymous · Accepted solution]

HI @sam_gift,

You can try to use the following measure formula to use the current description as conditions to redirect to different calculation expressions:

formula = 
VAR currDesc =
    SELECTEDVALUE ( 'Dimension'[Description] )
VAR kf1 =
    CALCULATE (
        SUM ( 'Fact'[Currency] ),
        FILTER ( ALLSELECTED ( 'Fact' ), 'Fact'[rep_item_id] = 1 )
    )
VAR kf2 =
    CALCULATE (
        SUM ( 'Fact'[Currency] ),
        FILTER ( ALLSELECTED ( 'Fact' ), 'Fact'[rep_item_id] = 2 )
    )
VAR kf3 =
    CALCULATE (
        SUM ( 'Fact'[Currency] ),
        FILTER ( ALLSELECTED ( 'Fact' ), 'Fact'[rep_item_id] = 3 )
    )
VAR kf4 = kf3 / kf2
VAR kf5 = kf1 * kf2
VAR kf6 =
    IF ( kf3 > 0, 1 - kf3 / kf1, 0 )
VAR _current =
    CALCULATE (
        SUM ( 'Fact'[Currency] ),
        FILTER (
            ALLSELECTED ( 'Fact' ),
            [rep_item_id] IN VALUES ( 'Dimension'[rep_item_id] )
        )
    )
RETURN
    SWITCH ( currDesc, "KF004", kf4, "KF005", kf5, "KF006", kf6, _current )

Regards,

Xiaoxin Sheng